autofilter average without zero

J

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
 
A

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average over autofilter results 4
Average of Subtotals in Pivot Table 0
SUMPRODUCT with Autofilter 3
SubTotal function to return number of rows returns only zero 4
Average non-consecutive cells excluding zero 8
average function 7
Average across worksheets ignoring zero 2
AVERAGE 7

Top