Average in Pivot Table
Shane, this is the data from the Pivot Table except the "Average Column" is
outside of the Pivot Table to illistrate the challenge. The reason I need to
calculate in side the Pivot Table is the actual average I am trying to get is
the average of the Grand Totals or the 88%. My problem is that the A3 (blank)
is being included to calculate the average.
8 10 11b Total Average
1 0.75 1 91.67% 91.7%
(blank) 0.75 1 58.33% 87.5%
1 0.5 1 83.33% 83.3%
1 0.5 0 50.00% 50.0%
1 1 1 100.00% 100.0%
(blank) 1 1 66.67% 100.0%
1 1 1 100.00% 100.0%
100% 79% 86% 79% 88%
Thanks again for you help.
"Shane Devenshire" wrote:
Hi,
You haven't told us what your base calculation is. With the dummy data you
provided I just change the base calculation to average and get exactly the
expected result in the Grand Total column.
I tested in 2003 and 2007.
Are A, B, and C different fields or different elements of a column field?
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire
"Jeff" wrote:
I have created a formula in a Pivot Table to average A,B and C.
I have tried the following with the same results:
=AVERAGE(A,B,C)
=Sum(A,B,C)/Count(A,B,C)
A B C Should be Formula
1 0.75 1 0.9167 91.67%
(blank) 0.75 1 0.875 58.33%
1 0.5 1 0.8333 83.33%
1 0.5 0 0.5 50.00%
1 1 1 1 100.00%
(blank) 1 1 1 66.67%
1 1 1 1 100.00%
Any other suggestions?
Thanks,
|