Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting number of contacts a day
So here's what I am trying to do, I have a source table with the following
entries: 00006 - Main Support Closed m 1/12/06 11:44 AM 00006 - Main Support Closed m 1/12/06 11:27 AM 00006 - Main Support Closed m 1/10/06 10:41 AM Two columns. What I want to do is create a chart where it shows for example Jan 12, there were 2 entries, and Jan 10, there were 1 entry. Ofcourse this has to be scalable to a larger set. I just want to see the trend of entries per day. I don't want a bunch of =Countif's if possible. Ideas? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting number of contacts a day
Assuming your two columns are Description and Date-Time, you can use a pivot
table to do this for you. select a cell in the range, and choose PivotTable and PivotChart Report from the Data menu, and select some reasonable options. Drag the Date-Time field into the Data area of the empty pivot table, then drag the field again into the Rows area. Without doing anything further, the pivot table looks like this: Sample Raw Data: what when a 1/15/2006 b 1/15/2006 c 1/15/2006 d 1/14/2006 e 1/14/2006 f 1/13/2006 g 1/13/2006 h 1/13/2006 i 1/12/2006 Pivot Table: Count of when When Total 1/12/2006 1 1/13/2006 3 1/14/2006 2 1/15/2006 3 Grand Total 9 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Sean McCloskey" <Sean wrote in message ... So here's what I am trying to do, I have a source table with the following entries: 00006 - Main Support Closed m 1/12/06 11:44 AM 00006 - Main Support Closed m 1/12/06 11:27 AM 00006 - Main Support Closed m 1/10/06 10:41 AM Two columns. What I want to do is create a chart where it shows for example Jan 12, there were 2 entries, and Jan 10, there were 1 entry. Ofcourse this has to be scalable to a larger set. I just want to see the trend of entries per day. I don't want a bunch of =Countif's if possible. Ideas? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting number of contacts a day
I'm facing a similar problem, and Jon, I find a pivot table to get me
very close to where I want to be, except that, like Sean, I have a dates field that looks like this: 1/12/06 10:20 AM 1/12/06 11:44 AM So two entries for January 12th, but different times. Now I can format the cells to show only the dates, but when I use a pivot chart, it sees both those rows as different, and will create a total of 1 for 10:20 am and one for 11:44 am. How do I get the pivot chart to ignore the time and only pay attention to the date? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting number of contacts a day
This is the Grouping feature I described in my last post. Here's another
example: Dummy data: Time 1/12/2006 10:20 1/12/2006 11:44 1/12/2006 13:08 1/12/2006 14:32 1/13/2006 10:20 1/13/2006 11:44 1/14/2006 10:20 1/14/2006 11:44 1/14/2006 13:08 1/14/2006 14:32 1/14/2006 15:56 1/14/2006 17:20 1/14/2006 18:44 1/14/2006 20:08 1/14/2006 21:32 1/14/2006 22:56 Make a pivot table, with the Time field in the Row area and in the Data area: Count of Time Time Total 1/12/2006 10:20 1 1/12/2006 11:44 1 1/12/2006 13:08 1 1/12/2006 14:32 1 1/13/2006 10:20 1 1/13/2006 11:44 1 1/14/2006 10:20 1 1/14/2006 11:44 1 1/14/2006 13:08 1 1/14/2006 14:32 1 1/14/2006 15:56 1 1/14/2006 17:20 1 1/14/2006 18:44 1 1/14/2006 20:08 1 1/14/2006 21:32 1 1/14/2006 22:56 1 Grand Total 16 Right click on the Time field button, choose Group and Show Detail, then choose Group. Unselect Months, and select Days: Count of time time Total 12-Jan 4 13-Jan 2 14-Jan 10 Grand Total 16 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Lawrence" wrote in message ups.com... I'm facing a similar problem, and Jon, I find a pivot table to get me very close to where I want to be, except that, like Sean, I have a dates field that looks like this: 1/12/06 10:20 AM 1/12/06 11:44 AM So two entries for January 12th, but different times. Now I can format the cells to show only the dates, but when I use a pivot chart, it sees both those rows as different, and will create a total of 1 for 10:20 am and one for 11:44 am. How do I get the pivot chart to ignore the time and only pay attention to the date? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
number format | Excel Discussion (Misc queries) | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) |