Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Grouping by the hour
I would like to bar chart a room usage count by the hour. When the room is
checked anytime during the hour it is added to the total for the hour. On the "Y" axis is the room usage count and on the "X" axis is the hour of the day. How should I go about doing this, considering the data below? (e.g. a bar would extend to the point 9:00 AM and 6) TIA for the help. -Brandon P time roomusagecount 8:41 AM 0 8:45 AM 0 9:30 AM 1 9:35 AM 0 9:35 AM 0 9:35 AM 1 9:40 AM 2 9:53 AM 2 10:10 AM 0 10:15 AM 1 10:15 AM 2 11:18 AM 1 11:31 AM 0 11:33 AM 2 12:56 PM 1 1:10 PM 2 1:10 PM 2 2:00 PM 1 2:09 PM 1 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Grouping by the hour
Make a pivot table of the data, with time in the row area and (sum of) usage
in the data area. Group the time field by hours: Sum of usage time Total 8 AM 0 9 AM 6 10 AM 3 11 AM 3 12 PM 1 1 PM 4 2 PM 2 Grand Total 19 Make a chart from this data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brandon" wrote in message ... I would like to bar chart a room usage count by the hour. When the room is checked anytime during the hour it is added to the total for the hour. On the "Y" axis is the room usage count and on the "X" axis is the hour of the day. How should I go about doing this, considering the data below? (e.g. a bar would extend to the point 9:00 AM and 6) TIA for the help. -Brandon P time roomusagecount 8:41 AM 0 8:45 AM 0 9:30 AM 1 9:35 AM 0 9:35 AM 0 9:35 AM 1 9:40 AM 2 9:53 AM 2 10:10 AM 0 10:15 AM 1 10:15 AM 2 11:18 AM 1 11:31 AM 0 11:33 AM 2 12:56 PM 1 1:10 PM 2 1:10 PM 2 2:00 PM 1 2:09 PM 1 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Grouping by the hour
I did exactly what you described, but it says, "Cannot Group That Selection"
when I attempt to group it. (I am attempting to group it by right-clicking on the header "time" and selecting "group") Do you know how I can resolve this? Is it the format of the time. The datasheet that this is coming from has the cells format as the time shown in the table. TIA, -Brandon P "Jon Peltier" wrote: Make a pivot table of the data, with time in the row area and (sum of) usage in the data area. Group the time field by hours: Sum of usage time Total 8 AM 0 9 AM 6 10 AM 3 11 AM 3 12 PM 1 1 PM 4 2 PM 2 Grand Total 19 Make a chart from this data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brandon" wrote in message ... I would like to bar chart a room usage count by the hour. When the room is checked anytime during the hour it is added to the total for the hour. On the "Y" axis is the room usage count and on the "X" axis is the hour of the day. How should I go about doing this, considering the data below? (e.g. a bar would extend to the point 9:00 AM and 6) TIA for the help. -Brandon P time roomusagecount 8:41 AM 0 8:45 AM 0 9:30 AM 1 9:35 AM 0 9:35 AM 0 9:35 AM 1 9:40 AM 2 9:53 AM 2 10:10 AM 0 10:15 AM 1 10:15 AM 2 11:18 AM 1 11:31 AM 0 11:33 AM 2 12:56 PM 1 1:10 PM 2 1:10 PM 2 2:00 PM 1 2:09 PM 1 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Grouping by the hour
If there are blank cells in the Time column, or cells with text, you'll
see that message. If that's the problem, there are a couple of fixes he http://www.contextures.com/xlPivot07.html#Problems Brandon P wrote: I did exactly what you described, but it says, "Cannot Group That Selection" when I attempt to group it. (I am attempting to group it by right-clicking on the header "time" and selecting "group") Do you know how I can resolve this? Is it the format of the time. The datasheet that this is coming from has the cells format as the time shown in the table. TIA, -Brandon P "Jon Peltier" wrote: Make a pivot table of the data, with time in the row area and (sum of) usage in the data area. Group the time field by hours: Sum of usage time Total 8 AM 0 9 AM 6 10 AM 3 11 AM 3 12 PM 1 1 PM 4 2 PM 2 Grand Total 19 Make a chart from this data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brandon" wrote in message ... I would like to bar chart a room usage count by the hour. When the room is checked anytime during the hour it is added to the total for the hour. On the "Y" axis is the room usage count and on the "X" axis is the hour of the day. How should I go about doing this, considering the data below? (e.g. a bar would extend to the point 9:00 AM and 6) TIA for the help. -Brandon P time roomusagecount 8:41 AM 0 8:45 AM 0 9:30 AM 1 9:35 AM 0 9:35 AM 0 9:35 AM 1 9:40 AM 2 9:53 AM 2 10:10 AM 0 10:15 AM 1 10:15 AM 2 11:18 AM 1 11:31 AM 0 11:33 AM 2 12:56 PM 1 1:10 PM 2 1:10 PM 2 2:00 PM 1 2:09 PM 1 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Grouping by the hour
That is exactly the problem. It causes my data summary processes to be less
automatic (since the pivot table range is defined to an exclusing number of rows). But, at least I am now able to group my data. Thanks, Brandon P "Debra Dalgleish" wrote: If there are blank cells in the Time column, or cells with text, you'll see that message. If that's the problem, there are a couple of fixes he http://www.contextures.com/xlPivot07.html#Problems Brandon P wrote: I did exactly what you described, but it says, "Cannot Group That Selection" when I attempt to group it. (I am attempting to group it by right-clicking on the header "time" and selecting "group") Do you know how I can resolve this? Is it the format of the time. The datasheet that this is coming from has the cells format as the time shown in the table. TIA, -Brandon P "Jon Peltier" wrote: Make a pivot table of the data, with time in the row area and (sum of) usage in the data area. Group the time field by hours: Sum of usage time Total 8 AM 0 9 AM 6 10 AM 3 11 AM 3 12 PM 1 1 PM 4 2 PM 2 Grand Total 19 Make a chart from this data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brandon" wrote in message ... I would like to bar chart a room usage count by the hour. When the room is checked anytime during the hour it is added to the total for the hour. On the "Y" axis is the room usage count and on the "X" axis is the hour of the day. How should I go about doing this, considering the data below? (e.g. a bar would extend to the point 9:00 AM and 6) TIA for the help. -Brandon P time roomusagecount 8:41 AM 0 8:45 AM 0 9:30 AM 1 9:35 AM 0 9:35 AM 0 9:35 AM 1 9:40 AM 2 9:53 AM 2 10:10 AM 0 10:15 AM 1 10:15 AM 2 11:18 AM 1 11:31 AM 0 11:33 AM 2 12:56 PM 1 1:10 PM 2 1:10 PM 2 2:00 PM 1 2:09 PM 1 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Grouping by the hour
Perhaps you can use a dynamic range as the source:
http://www.contextures.com/xlPivot01.html Brandon P wrote: That is exactly the problem. It causes my data summary processes to be less automatic (since the pivot table range is defined to an exclusing number of rows). But, at least I am now able to group my data. Thanks, Brandon P "Debra Dalgleish" wrote: If there are blank cells in the Time column, or cells with text, you'll see that message. If that's the problem, there are a couple of fixes he http://www.contextures.com/xlPivot07.html#Problems Brandon P wrote: I did exactly what you described, but it says, "Cannot Group That Selection" when I attempt to group it. (I am attempting to group it by right-clicking on the header "time" and selecting "group") Do you know how I can resolve this? Is it the format of the time. The datasheet that this is coming from has the cells format as the time shown in the table. TIA, -Brandon P "Jon Peltier" wrote: Make a pivot table of the data, with time in the row area and (sum of) usage in the data area. Group the time field by hours: Sum of usage time Total 8 AM 0 9 AM 6 10 AM 3 11 AM 3 12 PM 1 1 PM 4 2 PM 2 Grand Total 19 Make a chart from this data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brandon" wrote in message ... I would like to bar chart a room usage count by the hour. When the room is checked anytime during the hour it is added to the total for the hour. On the "Y" axis is the room usage count and on the "X" axis is the hour of the day. How should I go about doing this, considering the data below? (e.g. a bar would extend to the point 9:00 AM and 6) TIA for the help. -Brandon P time roomusagecount 8:41 AM 0 8:45 AM 0 9:30 AM 1 9:35 AM 0 9:35 AM 0 9:35 AM 1 9:40 AM 2 9:53 AM 2 10:10 AM 0 10:15 AM 1 10:15 AM 2 11:18 AM 1 11:31 AM 0 11:33 AM 2 12:56 PM 1 1:10 PM 2 1:10 PM 2 2:00 PM 1 2:09 PM 1 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Grouping by the hour
Hi Debra:
Thanks for the followup, I actually ran across that after the last message. I'll be giving it a try to see if it works. Thanks for all your help. :Brandon: P "Debra Dalgleish" wrote: Perhaps you can use a dynamic range as the source: http://www.contextures.com/xlPivot01.html Brandon P wrote: That is exactly the problem. It causes my data summary processes to be less automatic (since the pivot table range is defined to an exclusing number of rows). But, at least I am now able to group my data. Thanks, Brandon P "Debra Dalgleish" wrote: If there are blank cells in the Time column, or cells with text, you'll see that message. If that's the problem, there are a couple of fixes he http://www.contextures.com/xlPivot07.html#Problems Brandon P wrote: I did exactly what you described, but it says, "Cannot Group That Selection" when I attempt to group it. (I am attempting to group it by right-clicking on the header "time" and selecting "group") Do you know how I can resolve this? Is it the format of the time. The datasheet that this is coming from has the cells format as the time shown in the table. TIA, -Brandon P "Jon Peltier" wrote: Make a pivot table of the data, with time in the row area and (sum of) usage in the data area. Group the time field by hours: Sum of usage time Total 8 AM 0 9 AM 6 10 AM 3 11 AM 3 12 PM 1 1 PM 4 2 PM 2 Grand Total 19 Make a chart from this data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brandon" wrote in message ... I would like to bar chart a room usage count by the hour. When the room is checked anytime during the hour it is added to the total for the hour. On the "Y" axis is the room usage count and on the "X" axis is the hour of the day. How should I go about doing this, considering the data below? (e.g. a bar would extend to the point 9:00 AM and 6) TIA for the help. -Brandon P time roomusagecount 8:41 AM 0 8:45 AM 0 9:30 AM 1 9:35 AM 0 9:35 AM 0 9:35 AM 1 9:40 AM 2 9:53 AM 2 10:10 AM 0 10:15 AM 1 10:15 AM 2 11:18 AM 1 11:31 AM 0 11:33 AM 2 12:56 PM 1 1:10 PM 2 1:10 PM 2 2:00 PM 1 2:09 PM 1 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert decimal numbers to a fraction of an hour for payroll hour | Excel Worksheet Functions | |||
Subtracting date:hour from date:hour | Excel Worksheet Functions | |||
convert time from 60 minute hour to 100 minute hour | Excel Discussion (Misc queries) | |||
How can I round an hour to the nearest 1/4 hour? | Excel Worksheet Functions | |||
convert 100 minute hour to a 60 minute hour | Excel Worksheet Functions |