Solved

Graph based on Date Range

Posted on 2011-03-03
9
597 Views
Last Modified: 2012-05-11
I would like to be able to:
* Redraw graph based on date range
* Show PIE chart based on date range to show were time is being allocated
Additonally, is there way to format this data so as the weeks are appended on (up to a 52 weeks), the spreadsheet doesn't get cumbersome?
Workload-WIP.xlsx
0
Comment
Question by:singleton2787
  • 5
  • 4
9 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 35032207
Hello,

the chart in the attached file links to an external source. No way of doing anything with the chart unless you post the source data.

cheers, teylyn
0
 

Author Comment

by:singleton2787
ID: 35033380
I was going to point the source to the data in the same workbook I posted, apologies
0
 
LVL 50

Expert Comment

by:teylyn
ID: 35033509
Hello,

I see no way the chart and the data on the Data Entry sheet have anything in common. Where is the "Actual" data in the table? There are no dates in the source data.  The way your data is laid out in the Data Entry sheet does not lend itself to charting easily.

Can you explain in words what you want to chart?
0
 

Author Comment

by:singleton2787
ID: 35037570
Here is a newer version of the spreadsheet. The data and the graph are linked now.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:singleton2787
ID: 35037574
file...oops
Workload-WIP.xlsx
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 35041490
Hello,

thanks for the updated file. What do you mean by "redraw graph based on date range"? You don't have any dates in the source data, just text.

Any chart-related task would be much easier if your data were arranged in one contiguous table instead of interspersing it with blank rows. Your data has the form of a report. Create a data entry table with three fields

Date | Task | Value

You can then easily use Pivot tables to create summary reports like the one on your Data Entry sheet, and with just a few clicks can create dynamic charts.

And a little piece of advice: Don't use pie charts for series with more than two data points. 3D pie charts are even less readable, and the explosion in the 3D graph hides the data more than it exposes it. For example, the wedge at 9 o'clock looks a lot smaller than the wedge at 4-5 o'clock, although the data value is the same. The eye needs the number label to realise that the numbers are actually the same value. Also, one needs to constantly flick back and forth between the pie and the legend to understand which colour is which task. A much better way of presenting this data is a horizontal bar chart, where it is immediately apparent which bars are longer than others and text labels sit right with the bars.

In the attached file, please find some of your sample data re-arranged in a simple table. I created a pivot table report that looks like your Data Entry sheet. On the next tab, I created a Pivot chart. Use the drop-downs on the chart to select the data you want to show in the chart.

Below that, please find a horizontal bar chart as an alternative to the pie chart. Again, you can select with drop downs and change the scope of the chart.

Pivot tables and pivot charts are very powerful tools and can produce reports on large data sets with just a few clicks.

Hope that helps.

cheers, teylyn
Copy-of-Workload-WIP.xlsx
0
 

Author Closing Comment

by:singleton2787
ID: 35058522
Spectacular! Thanks!
0
 

Author Comment

by:singleton2787
ID: 35058725
Sorry, one last question... How do I make a single source of data entry? The columnar (Columns A,B,C on 'Data and pivot table report' tab appear to be where the Pivot grpahs are pulled from?
I would like the columns F-N be the data source for all, they would be easier to enter data on.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 35060813
Hello,

to change the source of a Pivot table, select the pivot table and on the Options Tab of the PivotTable Tools click Change Data Source. You will then get a dialog where you can select the range for the pivot table.

cheers, teylyn
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now