Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table Grouping (Excel 2000)- This has to be simple
I want to count the number of events that occur in 10 minute increments over
a period of days. I can easily count count the number of events, but the default is for each minute and I don't see any way to change it to 10 minute increments? Is this possible? Any help is appreciated. |
#2
|
|||
|
|||
Pivot Table Grouping (Excel 2000)- This has to be simple
You could add a column to your source data, with the formula:
=MINUTE(A2) where Time is in column A. Add the new field to your pivot table, and group on it. uncoolfester wrote: I want to count the number of events that occur in 10 minute increments over a period of days. I can easily count count the number of events, but the default is for each minute and I don't see any way to change it to 10 minute increments? Is this possible? Any help is appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Pivot Table Grouping (Excel 2000)- This has to be simple
I think this way you will still group by minute.
If you use FLOOR(MINUTE(A2)/10) it will retur0 for the minutes 0 to 9; 1 for the minutes 10 to 19 and so on. Run your pivot table grouping for this column and your data will be sorted by ten minutes interval. If your sample is more than one hour long you may want to do: CONCATENATE(HOUR(A2);":";FLOOR(MINUTE(A2)/10)) instead as yor grouping column "Debra Dalgleish" escreveu: You could add a column to your source data, with the formula: =MINUTE(A2) where Time is in column A. Add the new field to your pivot table, and group on it. uncoolfester wrote: I want to count the number of events that occur in 10 minute increments over a period of days. I can easily count count the number of events, but the default is for each minute and I don't see any way to change it to 10 minute increments? Is this possible? Any help is appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
Pivot Table Grouping (Excel 2000)- This has to be simple
The MINUTE formula will return a number between 0 and 60, and the
numbers can be grouped by 10, or any interval. Fred wrote: I think this way you will still group by minute. If you use FLOOR(MINUTE(A2)/10) it will retur0 for the minutes 0 to 9; 1 for the minutes 10 to 19 and so on. Run your pivot table grouping for this column and your data will be sorted by ten minutes interval. If your sample is more than one hour long you may want to do: CONCATENATE(HOUR(A2);":";FLOOR(MINUTE(A2)/10)) instead as yor grouping column "Debra Dalgleish" escreveu: You could add a column to your source data, with the formula: =MINUTE(A2) where Time is in column A. Add the new field to your pivot table, and group on it. uncoolfester wrote: I want to count the number of events that occur in 10 minute increments over a period of days. I can easily count count the number of events, but the default is for each minute and I don't see any way to change it to 10 minute increments? Is this possible? Any help is appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
Pivot Table Grouping (Excel 2000)- This has to be simple
Fred & Debra,
Thanks for the replies, you are getting me on track. For the benefit of others that may have read this, the syntax for the Floor function is FLOOR(MINUTE(A2),10). Is there an easy way to just round a date/time value into 10 minute increments then in the data sheet....for example my cell contains "7/25/2004 2:21:10 PM" which I would want to read round to "7/25/2004 2:20:00 PM". Each date/time would be rounded to the nearest 10 minute increment?? "Fred" wrote: I think this way you will still group by minute. If you use FLOOR(MINUTE(A2)/10) it will retur0 for the minutes 0 to 9; 1 for the minutes 10 to 19 and so on. Run your pivot table grouping for this column and your data will be sorted by ten minutes interval. If your sample is more than one hour long you may want to do: CONCATENATE(HOUR(A2);":";FLOOR(MINUTE(A2)/10)) instead as yor grouping column "Debra Dalgleish" escreveu: You could add a column to your source data, with the formula: =MINUTE(A2) where Time is in column A. Add the new field to your pivot table, and group on it. uncoolfester wrote: I want to count the number of events that occur in 10 minute increments over a period of days. I can easily count count the number of events, but the default is for each minute and I don't see any way to change it to 10 minute increments? Is this possible? Any help is appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
|
|||
|
|||
Pivot Table Grouping (Excel 2000)- This has to be simple
To the nearest 10th minute use
=ROUND(A1*144,0)/144 where A1 holds the date/time -- Regards, Peo Sjoblom "uncoolfester" wrote in message ... Fred & Debra, Thanks for the replies, you are getting me on track. For the benefit of others that may have read this, the syntax for the Floor function is FLOOR(MINUTE(A2),10). Is there an easy way to just round a date/time value into 10 minute increments then in the data sheet....for example my cell contains "7/25/2004 2:21:10 PM" which I would want to read round to "7/25/2004 2:20:00 PM". Each date/time would be rounded to the nearest 10 minute increment?? "Fred" wrote: I think this way you will still group by minute. If you use FLOOR(MINUTE(A2)/10) it will retur0 for the minutes 0 to 9; 1 for the minutes 10 to 19 and so on. Run your pivot table grouping for this column and your data will be sorted by ten minutes interval. If your sample is more than one hour long you may want to do: CONCATENATE(HOUR(A2);":";FLOOR(MINUTE(A2)/10)) instead as yor grouping column "Debra Dalgleish" escreveu: You could add a column to your source data, with the formula: =MINUTE(A2) where Time is in column A. Add the new field to your pivot table, and group on it. uncoolfester wrote: I want to count the number of events that occur in 10 minute increments over a period of days. I can easily count count the number of events, but the default is for each minute and I don't see any way to change it to 10 minute increments? Is this possible? Any help is appreciated. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table version issue | Excel Discussion (Misc queries) | |||
Pivot Table - Multiple consolidation Range | Excel Worksheet Functions | |||
Pivot Table Enhancements/Fixes in Excel 2003 | Excel Discussion (Misc queries) | |||
Pivot Table "Show All" Check Box missing in Excel 2000 | Excel Worksheet Functions | |||
Pivot table : Excel cannot complete this task with available resou | Excel Worksheet Functions |