#DIV/0! ...how to suppress when using this formula....

  • Thread starter Thread starter Randy Lefferts
  • Start date Start date
R

Randy Lefferts

=SUMIF(BJ3:BJ32,"<>0")/COUNTIF(BJ3:BJ32,"<>0")

I read a similar post where they needed to suppress the
error message above and the answer was to use =IF(COUNT
(B35:B47)>0,AVERAGE(B35:B47,"") to eliminate the error.
Of course, this was in response to the specific formula
the person was using.

So I thought that I could use =IF(COUNT(B35:B47)>0,
(insert my functions here),"") and that would work. It
doesn't appear to.

The spreadsheet has each month of the year, broken out by
days. When the current month is September, there is not
information in Oct, Nov and Dec. How to keep the #DIV/0!
error from appearing in those months using =SUMIF
(BJ3:BJ32,"<>0")/COUNTIF(BJ3:BJ32,"<>0") ?

Thank you !
 
This is an array formula so must be entered/edited with ctrl+shift+enter
=AVERAGE(IF(C9:C11="x",D9:D11))
 
Don,

This replaces my original formula? I am not sure how you
mean to use this in my situation. Any clarification is
most appreciated :)
 
You could just check first:

=if(countif(bj3:bj32,"<>0")=0,"",SUMIF(BJ3:BJ32,"<>0")/COUNTIF(BJ3:BJ32,"<>0"))
 
This returns a 0 value if there is an error.

=if(iserror(SUMIF(BJ3:BJ32,"<>0")/COUNTIF(BJ3:BJ32,"<>0")),0,SUMIF(BJ3:BJ32,"<>0")/COUNTIF(BJ3:BJ32,"<>0"))
 
Note that that formula will count blanks as "<>0", so will underestimate
the average if there are blanks in the range.

One array-entered (CTRL-SHIFT-ENTER or CMD-RETURN) workaround, if blanks
are a problem:

=SUM(BJ3:BJ32)/SUMPRODUCT(--(BJ3:BJ32<>0),--ISNUMBER(BJ3:BJ32))

which results in 0, not "" if there are no non-zero numbers in the range.

If you want "" instead, one way:

=IF(SUMPRODUCT(--ISNUMBER(BJ3:BJ32),--(BJ3:BJ32<>0)), SUM(BJ3:BJ32) /
SUMPRODUCT(--ISNUMBER(BJ3:BJ32),--(BJ3:BJ32<>0)), "")
 
Wonderful! Thank you very much, this works as I was
hoping. Thanks again :)

Thanks to everyone for the responses
 
Thanks for the correction/enhancement.

JE said:
Note that that formula will count blanks as "<>0", so will underestimate
the average if there are blanks in the range.

One array-entered (CTRL-SHIFT-ENTER or CMD-RETURN) workaround, if blanks
are a problem:

=SUM(BJ3:BJ32)/SUMPRODUCT(--(BJ3:BJ32<>0),--ISNUMBER(BJ3:BJ32))

which results in 0, not "" if there are no non-zero numbers in the range.

If you want "" instead, one way:

=IF(SUMPRODUCT(--ISNUMBER(BJ3:BJ32),--(BJ3:BJ32<>0)), SUM(BJ3:BJ32) /
SUMPRODUCT(--ISNUMBER(BJ3:BJ32),--(BJ3:BJ32<>0)), "")
Click to expand...
 
Back
Top