Pivot Table Grouping (Excel 2000)- This has to be simple

U

uncoolfester

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.
 
D

Debra Dalgleish

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.
 
F

Fred

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:
 
D

Debra Dalgleish

The MINUTE formula will return a number between 0 and 60, and the
numbers can be grouped by 10, or any interval.
 
U

uncoolfester

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??
 
P

Peo Sjoblom

To the nearest 10th minute use

=ROUND(A1*144,0)/144


where A1 holds the date/time
 
Top