Subtotal Avg Exclude "0" Values

K

Ken

Excel2003 ...

In Row 1 I have Subtotal "Avg" Function ... =subtotal(1,myrange)

Issue ... I wish to exclude "0" values from the calculation.

How do I write to do this? ... Thanks ... Kha
 
T

T. Valko

Assuming your averaging a filtered list.

Assume the data range is B5:B20:

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

Ken

T. ... All I have to say is ... You deserve to be an MVP ... Works Great ...

Thank you for supporting these boards ... Kha
 

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 (exclude 1 min & 1 max Value) 2
Calculate visible cells only? 3
Sumproduct? 5
Count Dates <Today? 3
Conditional SUBTOTAL in XL210 Table 7
Exclude a row from a SUM 2
Sum a Filtered List excluding Negative Numbers 6
SUM ... Numbers, Nulls "", & Div/0 5

Top