Formula to AVERAGE if greater than 0

[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

 

[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)

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]

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

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