Average in Pivot Table

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

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,
 
Not sure there's a way to get it up using calculated fields in a PT
But you could frame it up in a col adjacent to the PT
Assuming (PT) data as posted in cols A to C, from row2 down
use this in say, D2, array-entered,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=AVERAGE(IF(A2:C2>0,A2:C2))
Copy down

And if there's possible error values (#DIV/0, #N/A) occuring within the data
as well, something quite common in PT analysis,
use this in D2, array-entered, copied down:
=AVERAGE(IF(ISNUMBER(A2:C2),IF((A2:C2>0),A2:C2)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
 
Hey Max,

Thanks for the response. AVERAGE works for me outside of the pivot table. It
is inside that is my problem :-)
 
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?
 
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.
 
Back
Top