Formula to AVERAGE if greater than 0

  • Thread starter Thread starter RayD
  • Start date Start date
R

RayD

Hello,

I have an existing AVERAGING formula that is counting "zeros".
Is there a good way to have the formula AVERAGE only if > than zero?


=AVERAGE(Wk1:Wk52!N9)

Thanks
 
RayD wrote...
I have an existing AVERAGING formula that is counting "zeros".
Is there a good way to have the formula AVERAGE only if > than zero?

=AVERAGE(Wk1:Wk52!N9)
Click to expand...

For 3D (or 1D but across worksheets) you need to resort to trickery.
Something like the array formula

=AVERAGE(IF(N(INDIRECT("Wk"&ROW(INDIRECT("1:52"))&"!N9"))>0,
N(INDIRECT("Wk"&ROW(INDIRECT("1:52"))&"!N9"))))
 
RayD wrote...

For 3D (or 1D but across worksheets) you need to resort to trickery.
Something like the array formula

=AVERAGE(IF(N(INDIRECT("Wk"&ROW(INDIRECT("1:52"))&"!N9"))>0,
N(INDIRECT("Wk"&ROW(INDIRECT("1:52"))&"!N9"))))
Click to expand...

Wow!! I would to say that trick worked... Many THANKS
 
Back
Top