| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
charting how many widgets over time
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! |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
charting how many widgets over time
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/ _______ wrote in message oups.com... 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! |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
charting how many widgets over time
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. |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
charting how many widgets over time
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/ _______ wrote in message oups.com... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hot key for time? | New Users to Excel | |||
| Hot key for time? | Excel Worksheet Functions | |||
| Hot key for time? | Excel Discussion (Misc queries) | |||
| time sheet to calculate 2 different columns | Excel Worksheet Functions | |||
| Charting time? help please... | Charts and Charting in Excel | |||
Linear Mode
