Excel 2003 - Using the average sum after filtering

[Gagsy]

When I filter records and then use the average formula I get all the records rather than only those filtered.

I can get round this by indivisually selecting the cells in the column which have been filtered but is there an automatic method.

If I filer out 100 records I dont want to select each individual cell.

Appreciate any assistance

Thanks
Gagsy

 

[Molby]

Check out the Worksheet Subtotal function.

 

[Gagsy]

Thanks for your reply.

Copuld you give me more info please as Im not sure what you mean.

Thanks
Gagsy

 

[TonyJollans]

Hi Gagsy,

Have you tried the Subtotal Function?

The following two functions return the same result without filtering.

=SUBTOTAL(1,A2:A10)
=AVERAGE(A2:A10)

However, the Subtotal ignores rows which are filtered out, whilst the Average does not.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Gagsy]

That works a treat - Thank you v.much.