Displaying percentage in ranges in pivot table

[pamarty]

Hi,

I need to create a pivot table from raw data that has one of the
columns as percentage

C1 C2 C3 C4 C5 Percentage
AAA XXX FFF G1 6778 0.045
AAA XXX FFF G1 6778 0.050
AAA XXX FFF G2 6778 0.052
AAA XXX FFF G2 6778 0.066
AAA XXX FFF G3 6778 0.080
AAA XXX NNN G1 6778 0.045
AAA XXX NNN G2 6778 0.070
BBB YYY CCC G4 6778 0.045

The pivot table that I need to create should display values for G1, G2
etc based on percentages ranges as:
C1
-- C2
-C3
C4
 percentage < 5%
 percentage = 5%
 percentage > 5%

I tried to use grouping for the percentage field but it does not work.
I am new to pivot tables. Any help will be greatly appreciated.

Thanks,
pamarty

 

[Brian Taylor]

You are probably going to have to put a helper column with you data.
Something like this:

=if(C7 < .05,"<.05",if(C7=.05,"=.05",">.05")

Then include that column in your pivot table and put it in the row
section.

You might be able to finagle this into a calculated item, but excel is
pretty strict on what you can do with a calculated item.

 

[pamarty]

Thanks Brian. Excel permits only 7 levels of nesting of functions.My
ranges have to go from 4% upto 8% grouped by 0.05%.

Is there any other way to do this?

 

[Brian Taylor]

You could use a helper column to round the percentages to the nearest
..05% and then display that in the pivot table instead of the actual
percentage:

=ROUND(C6/0.0005,0)*0.0005