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!