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
 
Top