If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#DIV/0! Error - Need to display 0 or blank
I have the following formula used to compare a list of vendors to entered
data and the make the calculation. =SUMIF(A2:A98,I2,F2:F98)/J2 I want the cell to display either blank or 0. Is there a quick way to changer this without using the ISERROR function? There are currently 32 cells with this formula and could be added to as we add vendors. Thanks for any insights. -- Brooks W. |
#2
|
|||
|
|||
#DIV/0! Error - Need to display 0 or blank
Try
=IF(J2=0,0,SUMIF(A2:A98,I2,F2:F98)/J2) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Brooks W." wrote in message ... I have the following formula used to compare a list of vendors to entered data and the make the calculation. =SUMIF(A2:A98,I2,F2:F98)/J2 I want the cell to display either blank or 0. Is there a quick way to changer this without using the ISERROR function? There are currently 32 cells with this formula and could be added to as we add vendors. Thanks for any insights. -- Brooks W. |
#3
|
|||
|
|||
#DIV/0! Error - Need to display 0 or blank
Try this:
=IF(J2=0,"",SUMIF(A2:A98,I2,F2:F98)/J2) Hope this helps. Pete On Feb 12, 6:56 pm, Brooks W. wrote: I have the following formula used to compare a list of vendors to entered data and the make the calculation. =SUMIF(A2:A98,I2,F2:F98)/J2 I want the cell to display either blank or 0. Is there a quick way to changer this without using the ISERROR function? There are currently 32 cells with this formula and could be added to as we add vendors. Thanks for any insights. -- Brooks W. |
#4
|
|||
|
|||
#DIV/0! Error - Need to display 0 or blank
In order to avoid errors from appearing you need to trap them.
This is done either with the syntax =IF(ISERROR([function]),0,[function]) OR =IFERROR([function],0) if you're using Excel 2007. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Brooks W." wrote: I have the following formula used to compare a list of vendors to entered data and the make the calculation. =SUMIF(A2:A98,I2,F2:F98)/J2 I want the cell to display either blank or 0. Is there a quick way to changer this without using the ISERROR function? There are currently 32 cells with this formula and could be added to as we add vendors. Thanks for any insights. -- Brooks W. |
#5
|
|||
|
|||
#DIV/0! Error - Need to display 0 or blank
try = if(isnumber(j2),SUMIF(A2:A98,I2,F2:F98)/J2,"")
"Brooks W." wrote: I have the following formula used to compare a list of vendors to entered data and the make the calculation. =SUMIF(A2:A98,I2,F2:F98)/J2 I want the cell to display either blank or 0. Is there a quick way to changer this without using the ISERROR function? There are currently 32 cells with this formula and could be added to as we add vendors. Thanks for any insights. -- Brooks W. |
#6
|
|||
|
|||
#DIV/0! Error - Need to display 0 or blank
Thanks it worked and was easy to correct all the cells I need to correct.
-- Brooks W. "Chip Pearson" wrote: Try =IF(J2=0,0,SUMIF(A2:A98,I2,F2:F98)/J2) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Brooks W." wrote in message ... I have the following formula used to compare a list of vendors to entered data and the make the calculation. =SUMIF(A2:A98,I2,F2:F98)/J2 I want the cell to display either blank or 0. Is there a quick way to changer this without using the ISERROR function? There are currently 32 cells with this formula and could be added to as we add vendors. Thanks for any insights. -- Brooks W. |
Thread Tools | |
Display Modes | |
|
|