AVERAGE from different cells ignoring 0

[dinkss]

Hi there,

I want to use avergae function to calculate average from different cells like: (AB14,AB51,AB88,AB125,AB162,AB199) but ignoring 0, #DIV/0!.

What formula I have to use to calculate average from different cells?

Any help will be very appreciated.

Thank you.

 

[Domenic]

The following formula will average every 37th row, starting at AB14...

=AVERAGE(IF(MOD(ROW(AB14:AB199)-ROW(AB14),37)=0,IF(AB14:AB199>0,AB14:AB199)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[dinkss]

Thanks, worked fine for me! Great job!

 

[Domenic]

That's great to hear, thanks for the feedback.

Cheers!