# Average IF returns #DIV/0! error

1. ## Average IF returns #DIV/0! error

Hi Community Members...

In C5 I want to average the only negative numbers
if its entered in B9:B29. If there is no negative number C5 should be blank or "No data" message.

I used =AVERAGE(IF(B9:B29<0,B9:B29," ")), but it returns a #DIV/0! error.

I have looked at Excel Help but could not work....

All help much appreciated and I thank all for their
help and time with my inquiry.

Best Regards.

2. ## Re: Average IF returns #DIV/0! error

Try

=IFERROR(AVERAGE(IF(B9:B29<0,B9:B29,"")),"")

C+S+E

3. ## Re: Average IF returns #DIV/0! error

??? or even

=IFERROR(AVERAGEIF(B9:B29,"<0",B9:B29),"")

entered normally. You can use a similar formula using SUMIF and COUNTIF rather than SUM(IF... and COUNT(IF....

4. ## Re: Average IF returns #DIV/0! error

It doesn't work with negative entries. Cell left blank.

5. ## Re: Average IF returns #DIV/0! error

I opened your file from post #4, selected C5, pressed F2 (to enter edit mode), then pressed ctrl-shift-enter to array enter the formula, and it returned the value -20.25. It looks to me like you did not array enter the formula, which is why it returned the error. Is that the problem, or is there more to the question?

6. ## Re: Average IF returns #DIV/0! error

Excellent.. I got it..

Thanks everyone here for kind help.

7. ## Re: Average IF returns #DIV/0! error

Originally Posted by Glenn Kennedy
??? or even

=IFERROR(AVERAGEIF(B9:B29,"<0",B9:B29),"")

entered normally. You can use a similar formula using SUMIF and COUNTIF rather than SUM(IF... and COUNT(IF....
This formula worked! The help built in to the Excel app was useless. THANKS!!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)