Subtotal Avg Exclude "0" Values
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))
--
Biff
Microsoft Excel MVP
"Ken" wrote in message
...
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
|