ARRAY FORMULA-RETURN 0 INSTEAD OF #DIV/0

[Guest]

Here is my array formula. I would like it to return a ZERO instead of
#DIV/0. Have spent way too much time trying to make it work and thought some
kind soul out there would be willing to help me:

{=AVERAGE(IF(H3:H6<>0, H3:H6,""))}

Thanks in advance!

 

[RagDyeR]

Try This:

=IF((ISERR(AVERAGE(H3:H6))),"",AVERAGE(H3:H6))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Here is my array formula. I would like it to return a ZERO instead of
#DIV/0. Have spent way too much time trying to make it work and thought
some
kind soul out there would be willing to help me:

{=AVERAGE(IF(H3:H6<>0, H3:H6,""))}

Thanks in advance!

 

[Guest]

I am assuming you get these errors because the range is empty, you can use

=IF(COUNTBLANK(H3:H6)=ROWS(H3:H6),0,AVERAGE(IF(H3:H6<>0,H3:H6)))

entered with ctrl + shift & enter


Regards,

Peo Sjoblom

 

[Guest]

Thanks so much for your response, however your formula doesn't address the
zeroes that are in the array. Consequently, since the value are currently
31, 0, 0, 0 I get an answer of 7.5 instead of 31.

 

[Guest]

Actually, no. The range isn't empty. Currently it has the value of 31, 0,
0, and 0. Sorry I didn't mention that before.

 

[Guest]

It doesn't matter, my formula will disregard the zeros and as an extra
precaution it will not return an error if all cells are empty

Regards,

Peo Sjoblom

 

[Guest]

I entered the formula per your instructions. It worked fine, however, if I
replaced the 31 with a zero, I got the #DIV/0 error again.

 

[Guest]

OK, got you

=IF(COUNTIF(H3:H6,0)=COUNT(H3:H6),0,AVERAGE(IF(H3:H6<>0,H3:H6)))

entered with ctrl + shift & enter will work if all cells empty and all if
cells are 0 and will disregard zeros in the average


Regards,

Peo Sjoblom

 

[Guest]

PERFECT! Thank you SO MUCH!

OK, got you

=IF(COUNTIF(H3:H6,0)=COUNT(H3:H6),0,AVERAGE(IF(H3:H6<>0,H3:H6)))

entered with ctrl + shift & enter will work if all cells empty and all if
cells are 0 and will disregard zeros in the average


Regards,

Peo Sjoblom