Displaying percentage in ranges in pivot table

P

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
 
B

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top