Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 :-) |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Average Question | Excel Worksheet Functions | |||
Average in a Pivot Table | Excel Discussion (Misc queries) | |||
Pivot table average | Excel Discussion (Misc queries) | |||
How do I use a pivot table to get an average count? | Excel Discussion (Misc queries) | |||
How to Get Sum of the Average in Pivot Table? | Excel Discussion (Misc queries) |