Subtotal Avg Exclude "0" Values
 

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

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

Subtotal Avg Exclude "0" Values
 

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

Thank you for supporting these boards ... Kha

"T. Valko" wrote:

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

Subtotal Avg Exclude "0" Values
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
T. ... All I have to say is ... You deserve to be an MVP ... Works Great
...

Thank you for supporting these boards ... Kha

"T. Valko" wrote:

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