Average Question

[BobA]

In a pivot table I want to show the average for a column of numbers in the top cell excluding the last row (which is a total for the column.)

Ex:

A1
A2 6
A3 9
A4 7
A5 3
A6 2
A7 8
A8 35

A8 is the total for the column, and I want A1 to show the average for the column (excluding, of course, A8).

Thanks

 

[Claus Busch]

Hi Bob,

Am Tue, 28 Jan 2014 08:18:07 -0800 (PST) schrieb BobA:

In a pivot table I want to show the average for a column of numbers in the top cell excluding the last row (which is a total for the column.)

Ex:

A1
A2 6
A3 9
A4 7
A5 3
A6 2
A7 8
A8 35

A8 is the total for the column, and I want A1 to show the average for the column (excluding, of course, A8).

try:
=AVERAGE(OFFSET($A$2,,,COUNT($A$2:$A$100)-1))


Regards
Claus B.

 

[BobA]

Hi Bob,

Am Tue, 28 Jan 2014 08:18:07 -0800 (PST) schrieb BobA:
- show quoted text -
try:
=AVERAGE(OFFSET($A$2,,,COUNT($A$2:$A$100)-1))


Regards
Claus B.

Thank you, Claus. I wish I understood this formula better, specifically the offset function and the three commas after $A$2,,,
but it works perfectly and is just what I needed.

Much appreciated