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


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

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!

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!

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!

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!

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!

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!

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!