Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying percentage in ranges in pivot table
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying percentage in ranges in pivot table
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying percentage in ranges in pivot table
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying percentage in ranges in pivot table
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating pivot table to include additional rows | Excel Discussion (Misc queries) | |||
pivot table | Excel Discussion (Misc queries) | |||
pivot table sort entries that don't yet appear in table | Excel Discussion (Misc queries) | |||
Pivot table formatting | Excel Discussion (Misc queries) | |||
In excel and a pivot table - how can I stop it displaying (blank). | Excel Discussion (Misc queries) |