#DIV/0! error

[jgold20]

I understand why I am receiving the error, is there a way to do error handling for the below code when "A3" is blank and "I3" = "YES"


=IF(OR(C3="",C3="WG"),IF(AND(I3="YES"),"",""),
IF(OR(C3="GE"),IF(AND(I3="YES"),(F3/A3),""),
IF(OR(C3="H",C3="SR"),IF(AND(I3="YES"),(F3/1.05/A3),""),
IF(OR(C3="B",C3="D",C3="L",C3="M",C3="N",C3="Q",C3="R",C3="V",C3="VP",C3="W",C3="Y"),IF(AND(I3="YES"),(F3/1.1/A3),""),
IF(OR(C3="DT",C3="X"),IF(AND(I3="YES"),(F3/1.12/A3),""),
IF(OR(C3="MD",C3="MN",C3="NO"),IF(AND(I3="YES"),(F3/1.13/A3),"")))))))

 

[jgold20]

I will give it a try in 5 minutes and let you know the results. I had to add a new column for division purposes (I3 is now J3 and I am dividing from F3). Ty

 

[Peter_SSs]

I will give it a try in 5 minutes and let you know the results. I had to add a new column for division purposes (I3 is now J3 and I am dividing from F3). Ty

Are you sure you need to add that new column? I'm not sure either way but suggest trying my formula with the original layout too.

My last formula can also be shortened up a bit as follows:

=IF(OR(I3<>"YES",A3="",C3="",C3="WG"),"",
IF(C3="GE",F3/A3,IF(OR(C3={"H","SR"}),F3/1.05/A3,
IF(OR(C3={"B","D","L","M","N","Q","R","V","VP","W","Y"}),F3/1.1/A3,
IF(OR(C3={"DT","X"}),F3/1.12/A3,
IF(OR(C3={"MD","MN","NO"}),F3/1.13/A3))))))

 

[Yongle]

Simply paste your formula as the first argument like this
IFERROR(IF(OR(C3="",C3="WG"),IF(AND(I3="YES"),"",""),IF(OR(C3="GE"),IF(AND(I3="YES"),(F3/A3),""),IF(OR(C3="H",C3="SR"),IF(AND(I3="YES"),(F3/1.05/A3),""),IF(OR(C3="B",C3="D",C3="L",C3="M",C3="N",C3="Q",C3="R",C3="V",C3="VP",C3="W",C3="Y"),IF(AND(I3="YES"),(F3/1.1/A3),""),IF(OR(C3="DT",C3="X"),IF(AND(I3="YES"),(F3/1.12/A3),""),IF(OR(C3="MD",C3="MN",C3="NO"),IF(AND(I3="YES"),(F3/1.13/A3),""))))))) ,"")

Or use Peter's shortened version of your formula

 

[jgold20]

Reply to #9
Peter,
I am using the cleaned up formula, it works, however I think I have a chicken and egg situation. When J3=YES, and cell F3 is blank (before I enter a numeric value) I am back to the #DIV error.

=IF(OR(J3<>"YES",C3="",C3="WG"),"",
IF(C3="GE",F3/A3,IF(OR(C3="H",C3="SR"),G3/1.05/F3,
IF(OR(C3="B",C3="D",C3="L",C3="M",C3="N",C3="Q",C3="R",C3="V",C3="VP",C3="W",C3="Y"),G3/1.1/F3,
IF(OR(C3="DT",C3="X"),G3/1.12/F3,
IF(OR(C3="MD",C3="MN",C3="NO"),G3/1.13/F3))))))

 

[jgold20]

Works perfectly - thank you

=iferror(if(or(j3<>"yes",c3="",c3="wg"),"",
if(c3="ge",f3/a3,if(or(c3="h",c3="sr"),g3/1.05/f3,
if(or(c3="b",c3="d",c3="l",c3="m",c3="n",c3="q",c3="r",c3="v",c3="vp",c3="w",c3="y"),g3/1.1/f3,
if(or(c3="dt",c3="x"),g3/1.12/f3,
if(or(c3="md",c3="mn",c3="no"),g3/1.13/f3)))))),"")

 

[Peter_SSs]

Reply to #9
Peter,
I am using the cleaned up formula, ...

No, you are using a different formula - you have removed one of the conditions from the first OR() function - my formula had 4 conditions, yours only has 3.

Adjusting for the changed columns (I think) my formula would be

=IF(OR(J3<>"YES",F3="",C3="",C3="WG"),"",
IF(C3="GE",G3/F3,IF(OR(C3={"H","SR"}),G3/1.05/F3,
IF(OR(C3={"B","D","L","M","N","Q","R","V","VP","W","Y"}),G3/1.1/F3,
IF(OR(C3={"DT","X"}),G3/1.12/F3,
IF(OR(C3={"MD","MN","NO"}),G3/1.13/F3))))))


The IFERROR wrapped around should not be a problem, but it should also not be needed - at least not needed to deal with F3 being blank.

 

[jgold20]

Updated formula works. Ty

 

[jgold20]

Is it a problem if I have each different condition on a separate line?:

=IF(OR(J3<>"YES",F3="",C3="",C3="WG"),"",
IF(C3="GE",G3/F3,
IF(OR(C3={"H","SR"}),G3/1.05/F3,
IF(OR(C3={"B","D","L","M","N","Q","R","V","VP","W","Y"}),G3/1.1/F3,
IF(OR(C3={"DT","X"}),G3/1.12/F3,
IF(OR(C3={"MD","MN","NO"}),G3/1.13/F3))))))

 

[Peter_SSs]

Updated formula works. Ty

You are welcome.

Is it a problem if I have each different condition on a separate line?:

=IF(OR(J3<>"YES",F3="",C3="",C3="WG"),"",
IF(C3="GE",G3/F3,
IF(OR(C3={"H","SR"}),G3/1.05/F3,
IF(OR(C3={"B","D","L","M","N","Q","R","V","VP","W","Y"}),G3/1.1/F3,
IF(OR(C3={"DT","X"}),G3/1.12/F3,
IF(OR(C3={"MD","MN","NO"}),G3/1.13/F3))))))

Not quite sure what you are asking. If it is about the physical layout of the formula, then that doesn't matter. The formula could just as well be written in a single line:

=IF(OR(J3<>"YES",F3="",C3="",C3="WG"),"",IF(C3="GE",G3/F3,IF(OR(C3={"H","SR"}),G3/1.05/F3,IF(OR(C3={"B","D","L","M","N","Q","R","V","VP","W","Y"}),G3/1.1/F3,IF(OR(C3={"DT","X"}),G3/1.12/F3,IF(OR(C3={"MD","MN","NO"}),G3/1.13/F3))))))

If that is not what you were asking about then you'll have to try to explain again.

 

[jgold20]

That is what I was asking. Ty