charting how many widgets over time

[tallone468]

Hi all!

I'm trying to build a chart that I would have thought should have been
simple, but for some reason I am having nothing but trouble getting it
to work. Basically, I have three worksheets that have several thousand
log entries. Each log entry is assigned a type code -- let's say for
the sake of argument each log entry is either an Apple an Orange or a
Banana. The log entry also contains a field with the timestamp of when
it was logged in. Simply, I just want to graph how many total Apples,
or Oranges, or Bananas I have over time -- dates on the x-axis, and a
running count of the type on the y-axis. Ultimately, I would like to
compare how fast Apples, Oranges and Bananas accumulate on the three
different worksheets (each is a different project).

Should be easy, right? Please help! I just can't seem to figure it
out!

 

[Jon Peltier]

It's not too hard if you know pivot tables. I started with data like this
(long post, sorry, and there are two columns, time and item):

time item
4/29/06 12:20 AM banana
4/29/06 2:38 AM banana
4/29/06 3:36 AM apple
4/29/06 5:57 AM apple
4/29/06 7:58 AM apple
4/29/06 10:05 AM cherry
4/29/06 11:02 AM cherry
4/29/06 11:50 AM apple
4/29/06 1:21 PM banana
4/29/06 2:30 PM cherry
4/29/06 4:09 PM apple
4/29/06 4:57 PM cherry
4/29/06 6:51 PM apple
4/29/06 8:56 PM banana
4/29/06 9:06 PM cherry
4/29/06 11:21 PM apple
4/30/06 1:22 AM cherry
4/30/06 3:13 AM cherry
4/30/06 4:49 AM apple
4/30/06 6:08 AM cherry
4/30/06 6:28 AM banana
4/30/06 6:57 AM apple
4/30/06 9:02 AM cherry
4/30/06 9:15 AM cherry
4/30/06 10:03 AM cherry
4/30/06 10:59 AM apple
4/30/06 12:20 PM cherry
4/30/06 2:24 PM banana
4/30/06 4:42 PM banana
4/30/06 5:30 PM banana
4/30/06 7:20 PM banana
4/30/06 8:24 PM banana
4/30/06 10:10 PM cherry
4/30/06 11:40 PM cherry
5/1/06 1:33 AM apple
5/1/06 2:06 AM apple
5/1/06 2:30 AM banana
5/1/06 4:12 AM apple
5/1/06 5:04 AM banana
5/1/06 6:08 AM apple
5/1/06 6:48 AM cherry
5/1/06 8:40 AM cherry
5/1/06 8:51 AM banana
5/1/06 9:59 AM apple
5/1/06 11:12 AM banana
5/1/06 1:07 PM cherry
5/1/06 3:28 PM banana
5/1/06 5:42 PM cherry
5/1/06 7:35 PM banana
5/1/06 9:55 PM apple
5/2/06 12:06 AM apple
5/2/06 12:25 AM apple
5/2/06 1:10 AM cherry
5/2/06 2:32 AM cherry
5/2/06 4:17 AM apple
5/2/06 4:20 AM apple
5/2/06 6:23 AM cherry
5/2/06 6:35 AM cherry
5/2/06 7:26 AM apple
5/2/06 8:46 AM cherry
5/2/06 9:13 AM banana
5/2/06 9:16 AM cherry
5/2/06 9:17 AM apple
5/2/06 9:25 AM cherry
5/2/06 10:50 AM cherry
5/2/06 11:19 AM cherry
5/2/06 12:52 PM banana
5/2/06 1:07 PM banana
5/2/06 2:40 PM banana
5/2/06 4:40 PM cherry
5/2/06 5:28 PM banana
5/2/06 6:59 PM banana
5/2/06 8:05 PM apple
5/2/06 8:49 PM apple
5/2/06 9:13 PM cherry
5/2/06 10:35 PM banana
5/2/06 10:54 PM apple
5/3/06 12:59 AM banana
5/3/06 2:04 AM cherry
5/3/06 2:22 AM apple
5/3/06 2:29 AM apple
5/3/06 3:10 AM cherry
5/3/06 5:20 AM banana
5/3/06 5:25 AM apple
5/3/06 6:01 AM apple
5/3/06 6:21 AM banana
5/3/06 7:48 AM banana
5/3/06 8:56 AM banana
5/3/06 11:02 AM apple
5/3/06 11:23 AM apple
5/3/06 12:22 PM cherry
5/3/06 1:28 PM cherry
5/3/06 2:22 PM apple
5/3/06 3:35 PM cherry
5/3/06 5:07 PM cherry
5/3/06 5:38 PM banana
5/3/06 6:42 PM apple
5/3/06 6:51 PM banana
5/3/06 8:57 PM banana
5/3/06 10:13 PM cherry
5/3/06 10:51 PM apple
5/4/06 12:51 AM banana
5/4/06 1:02 AM cherry
5/4/06 2:40 AM cherry
5/4/06 3:20 AM banana

I selected one cell in the range and created a pivot table (Data menu).
Excel auomatically expands the data source until it reaches a blank row or
column. I dragged item to the columns area, time to the rows area, and item
again to the data area. This gave me one row of pivot table per row of
table, which is not too useful. But right click on the time field button and
choose Group and Show Detail, then Group, and select days in the list, and
change the start to 4/29/06 (not 4/29/06 12:20 AM). This gives you a list by
day:

Count of item item
time apple banana cherry Grand Total
29-Apr 7 4 5 16
30-Apr 3 6 9 18
1-May 6 6 4 16
2-May 9 7 11 27
3-May 9 8 7 24
4-May 2 2 4
Grand Total 34 33 38 105

Now double click on the Count of item field button, click the Options>>
button, and under Show Data As, select Running Total In, and below that
select time as the base field.

Count of item item
time apple banana cherry Grand Total
29-Apr 7 4 5 16
30-Apr 10 10 14 34
1-May 16 16 18 50
2-May 25 23 29 77
3-May 34 31 36 101
4-May 34 33 38 105
Grand Total

If your data for the three fruits is on three different sheets, make three
pivot tables. A pivot table can go onto a different sheet than the data, so
you could put all three pivot tables together.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[tallone468]

Greatl, thanks, Jon. That all worked great. So now I have three
charts based on the three pivot tables for each project. But how do I
create a chart that will compare apples across all projects, oranges
across all three projects, etc.?

Excel does not seem to allow me to choose source data within a pivot
table to build one chart for this...

Thanks.

 

[Jon Peltier]

Select a blank cell away from any pivot table. Start the chart wizard, and
select a chart type in step 1. In step 2, click on the Series tab, and add
each series separately. This will prevent the chart from being converted
into a pivot chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______