Creating Bandings with Pivot

[AndrewKent]

Hi folks,

I was wondering if anyone could help me optimize a Pivot Table.

I have somewhere in the region of 20K rows of data. One of the columns contains a decimal figure, which can be anything from 1.00 up to 17.00. Each row contains a decimal within that range.

Now, what I want to be able to do is create a Pivot Table that will display these 20K records but "band" the decimals into 10 groups. So for example;

>= 1.00 - < 1.25
>= 1.25 - < 1.50

The groups of course won't be even but that's not important.

I could do this by adding a formula column containing an elaborate IF function to do this, then link the Pivot Table to it, but I was wondering if there is any way to get this result without having to add extra columns?

Andy

 

[RoryA]

Add that field as a row field and then right-click it and choose Group.

 

[AndrewKent]

Exactly what I needed thank you!

Is there any way to be more specific with the groupings, for example if I wanted to sat 1.00 to 1.25, 1.25 to 1.50, 1.50 to 2.50?

Andy

 

[RoryA]

No, they have to be regular. If you need irregular groupings you should use a table of bands and a lookup formula column in the source data.