ExcelBanter - ARRAY FORMULA-RETURN 0 INSTEAD OF #DIV/0

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - ARRAY FORMULA-RETURN 0 INSTEAD OF #DIV/0 (https://www.excelbanter.com/excel-worksheet-functions/9233-array-formula-return-0-instead-div-0-a.html)

Teri

ARRAY FORMULA-RETURN 0 INSTEAD OF #DIV/0

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!
==============================================

"Teri" wrote in message
...
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!

Peo Sjoblom

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

"Teri" wrote:

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!

Teri

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.

"RagDyeR" wrote:

Try This:

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

HTH,

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

"Teri" wrote in message
...
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!

Teri

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.

"Peo Sjoblom" wrote:

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

"Teri" wrote:

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!

Peo Sjoblom

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

"Teri" wrote:

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.

"Peo Sjoblom" wrote:

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

"Teri" wrote:

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!

Teri

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.

"Peo Sjoblom" wrote:

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

"Teri" wrote:

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.

"Peo Sjoblom" wrote:

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

"Teri" wrote:

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!

Peo Sjoblom

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

"Teri" wrote:

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.

"Peo Sjoblom" wrote:

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

"Teri" wrote:

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.

"Peo Sjoblom" wrote:

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

"Teri" wrote:

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!

Teri

PERFECT! Thank you SO MUCH!

"Peo Sjoblom" wrote:

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

"Teri" wrote:

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.

"Peo Sjoblom" wrote:

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

"Teri" wrote:

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.

"Peo Sjoblom" wrote:

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

"Teri" wrote:

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!

All times are GMT +1. The time now is 05:10 AM.