Average in Pivot Table
 

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,

Average in Pivot Table
 

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:C20,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:C20),A2:C2)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"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?

Average in Pivot Table
 

Hey Max,

Thanks for the response. AVERAGE works for me outside of the pivot table. It
is inside that is my problem :-)



"Max" wrote:

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:C20,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:C20),A2:C2)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"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?

Average in Pivot Table
 

As stated, I don't think its possible to effect in the PT itself
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Jeff" wrote:
Hey Max,

Thanks for the response. AVERAGE works for me outside of the pivot table. It
is inside that is my problem :-)

Average in Pivot Table
 

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,

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,