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/
_______
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!
>
Similar Threads
