Percentages in Pivot Tables

[Kate T]

I am using pivot charts in Excel 2003, so that I can use the selector arrows
to look at data split different ways on the same chart.
At the moment the pivot table that I am basing my chart is in this form:

Grade Point #F #M
A 1 x p
2 y q
A total z r
B 1 d m
2 e n
B total f o

Because we have many more F than M, pure numbers are not very helpful. I'd
like to replace or add to the data to express x and y as a percentage of z, p
and q as % of r, d and e as % of f, m and n as % of o. So far I've only been
able to get a % of the whole column.
I can put formulae next to the table, but then the selector arrows do not
work in the pivot chart.

Any help much appreciated!

 

[Bernie Deitrick]

Kate,

Unfortunately, this is one of those cases where you need to add columns to your data table to do the
calc as a "pre-calc".

For example, if you have Grade in column A, Point in B, #F in C, and #M in D, with headers in row 1,
then in E2, use the formula

=C2/SUMIF($A:$A,$A2,$C:$C)
Copy this to F2, then copy E2:F2 down to match your table.

Then use columns E and F as your data fields.

This won't allow you to hide and show items in "Point", but it will allow you to do "Grade" .... a
lot depends on what functionality you need.

HTH,
Bernie
MS Excel MVP