ARRAY FORMULA-RETURN 0 INSTEAD OF #DIV/0

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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!
 
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
 
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.
 
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.
 
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
 
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.
 
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
 
PERFECT! Thank you SO MUCH!

Peo Sjoblom said:
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
Click to expand...
 
Back
Top