autofilter average without zero

[jane]

I am using the Subtotal with autofilter to average the visible cells but I
need to exclude zero values.

please help.

thank you in advance, jane

 

[Ashish Mathur]

Hi,

In the Auto Filter drop down, exclude the 0 value by using "Custom". This
will show all cells based on the original filter applied by you and all the
cells which are not equal to 0.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

Assume the full unfiltered range is A5:B20 and you want to average B5:B20
excluding 0s:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B5:B20,ROW(B5:B20)-ROW(B5),0,1)),--(B5:B20<>0))/SUMPRODUCT(SUBTOTAL(2,OFFSET(B5:B20,ROW(B5:B20)-ROW(B5),0,1)),--(B5:B20<>0))