Percentages in Pivot Tables
 

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!

Percentages in Pivot Tables
 

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


"Kate T" wrote in message
...
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!