Averaging data after using autofilter

[Garth]

I am trying to average column data after using the
autofilter and I obtain the wrong result or error
messages. Cells are formatted correctly and the function
cell reference is correct. Averaging will work if the
values are typed in but does not seem to work after
autofiltering. Copy and Special paste does not solve the
problem. Any clues please?

 

[DDM]

Garth, don't use the AVERAGE function on a filtered list; use the SUBTOTAL
function instead. It was designed for just this kind of situation.

This is the formula you need: =SUBTOTAL(101,Range)

The 101 tells SUBTOTAL to average the range, counting visible cells only.

When you have time, search the topic "Subtotal Worksheet Function" in Excel
help.

 

[Andy B]

Hi

Have a look at SUBTOTAL() in help (under 'SUBTOTAL worksheet function').
This is designed to assist with autofiltered lists, and includes a counst,
sum and average option amongst others.

 

[Gord Dibben]

Garth

Try =SUBTOTAL(1,Range)

101 will return an error.

Gord Dibben Excel MVP

 

[DDM]

Ooops! I've got to be more careful of my versions.

Use =SUBTOTAL(101,Range) in Excel 2003; =SUBTOTAL(1,Range) in earlier
versions.

 

[Gord Dibben]

DDM

Thanks for that version clarification.

Not running Excel 2003 so did not know the difference.

Another seemingly unnecessary change by MS!

Gord

 

[Peo Sjoblom]

Hi Gord,

actually it is a new feature, the old one is still there but 101 will also
exclude
hidden rows/columns as in format>row/column>hide while 1 is still the same
excluding
filtered rows

 

[Gord Dibben]

Thanks Peo

So, not a change, but an addition.

Gord

 

[Guest]

Many thanks to everyone for your helpful advice.

----- Gord Dibben wrote: -----

Thanks Peo

So, not a change, but an addition.

Gord