| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
#DIV/0! ...how to suppress when using this formula....
=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 ! |
|
#3
|
|||
|
|||
|
Don,
This replaces my original formula? I am not sure how you mean to use this in my situation. Any clarification is most appreciated :) -----Original Message----- This is an array formula so must be entered/edited with ctrl+shift+enter =AVERAGE(IF(C9:C11="x",D9:D11)) -- Don Guillett SalesAid Software "Randy Lefferts" wrote in message ... =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 ! . |
|
#4
|
|||
|
|||
|
You could just check first:
=if(countif(bj3:bj32,"<0")=0,"",SUMIF(BJ3:BJ32,"< 0")/COUNTIF(BJ3:BJ32,"<0")) Randy Lefferts wrote: =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 ! -- Dave Peterson |
|
#5
|
|||
|
|||
|
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)), "") In article , Dave Peterson wrote: You could just check first: =if(countif(bj3:bj32,"<0")=0,"",SUMIF(BJ3:BJ32,"< 0")/COUNTIF(BJ3:BJ32,"<0") ) Randy Lefferts wrote: =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 ! |
|
#6
|
|||
|
|||
|
Thanks for the correction/enhancement.
JE McGimpsey wrote: 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)), "") In article , Dave Peterson wrote: You could just check first: =if(countif(bj3:bj32,"<0")=0,"",SUMIF(BJ3:BJ32,"< 0")/COUNTIF(BJ3:BJ32,"<0") ) Randy Lefferts wrote: =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 ! -- Dave Peterson |
|
#7
|
|||
|
|||
|
Wonderful! Thank you very much, this works as I was
hoping. Thanks again :) Thanks to everyone for the responses -----Original Message----- You could just check first: =if(countif(bj3:bj32,"<0")=0,"",SUMIF (BJ3:BJ32,"<0")/COUNTIF(BJ3:BJ32,"<0")) Randy Lefferts wrote: =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 ! -- Dave Peterson . |
|
#8
|
|||
|
|||
|
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")) "Randy Lefferts" wrote in message ... =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 ! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| suppress zero values in line charts? | Charts and Charting in Excel | |||
| suppress zero values in line charts? | Charts and Charting in Excel | |||
| formula help | Charts and Charting in Excel | |||
| hyperlink formula | Excel Discussion (Misc queries) | |||
| DIV/0 vs 0.00 | Excel Discussion (Misc queries) | |||
Hybrid Mode
