Solved

Graph based on Date Range

Posted on 2011-03-03
9
604 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 50
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
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:singleton2787
ID: 35037570
Here is a newer version of the spreadsheet. The data and the graph are linked now.
0
 

Author Comment

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

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

691 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