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

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

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

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

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

> 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]

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

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

"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
> >
> >

[Peo Sjoblom]

To the nearest 10th minute use

=ROUND(A1*144,0)/144


where A1 holds the date/time


--

Regards,

Peo Sjoblom

"uncoolfester" <[email protected]> wrote in message
news:[email protected]...
> 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
> > >
> > >