Formula to AVERAGE if greater than 0

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
 
H

Harlan Grove

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"))))
 
R

RayD

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
 
Top