# Average Question

B

#### 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

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.

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