autofilter average without zero
 

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

autofilter average without zero
 

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

"jane" wrote in message
...
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

autofilter average without zero
 

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

--
Biff
Microsoft Excel MVP


"jane" wrote in message
...
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