#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),"")))))))

 

[Peter_SSs]

.. Is there a way to do error handling for the below code when "A3" is blank and "I3" = "YES"

Yes there is. What do you want returned when that happens?


BTW, you have quite a lot of extra "baggage" in that formula. for example
AND(I3="YES") Used many times, is more simply written as I3="YES"
OR(C3="GE") is more simply written as C3="GE"

 

[Yongle]

Is this what you want?
- inserts "" where both conditions met otherwise evaluates original formula

=IF(AND(ISBLANK(A3),I3="YES"),"",your formula)

 

[jgold20]

I want the cell to be blank. With the change you provided, it returns FALSE

 

[jgold20]

I want the cell to be empty

 

[Yongle]

The method works
- try this formula and vary the value in A3 and I3
- 0/0 is only evaluated if AND results in FALSE

=IF(AND(ISBLANK(A3),I3="YES"),"",0/0)

Did you remove the leading = from beginning of your formula?

ie
=IF(AND(ISBLANK(A3),I3="YES"),"",IF(OR.....
not
=IF(AND(ISBLANK(A3),I3="YES"),"",=IF(OR.....

 

[Yongle]

Another way to suppress errors...

=IFERROR(your formula without a leading =,"")

 

[jgold20]

I had a comma missing, however, I had to make J3=NO (I had to create new cells, this moved over) to get the desired results (suppressing the #DIV error). This now has created a #DIV error when J3=YES (I had to create new cells, this moved over) before I enter a value. I do not think there is a way around this:

=IF(OR(C3="",C3="WG"),IF(AND(ISBLANK(F3),J3="NO"),"",""),
IF(OR(C3="GE"),IF(AND(ISBLANK(F3),J3="NO"),"",G3/F3),
IF(OR(C3="H",C3="SR"),IF(AND(ISBLANK(F3),J3="YES"),"",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"),IF(AND(ISBLANK(F3),J3="NO"),"",G3/1.1/F3),
IF(OR(C3="DT",C3="X"),IF(AND(ISBLANK(F3),J3="NO"),"",G3/1.12/F3),
IF(OR(C3="MD",C3="MN",C3="NO"),IF(AND(ISBLANK(F3),J3="NO"),"",G3/1.13/F3)))))))

F3 G3 H3 I3 J3
[TABLE="width: 658"]
<tbody>[TR]
[TD="class: xl80, width: 93, align: center"][/TD]
[TD="class: xl75, width: 96, align: center"][/TD]
[TD="class: xl76, width: 109, align: center"]#DIV /0![/TD]
[TD="class: xl72, width: 116, align: center"]YES[/TD]
[TD="class: xl73, width: 124, align: center"]YES[/TD]
[TD="class: xl77, width: 120"][/TD]
[/TR]
[TR]
[TD="class: xl80, width: 93, align: center"][/TD]
[TD="class: xl75, width: 96, align: center"][/TD]
[TD="class: xl76, align: center"]#DIV /0![/TD]
[TD="class: xl74"][/TD]
[TD="class: xl78, width: 244, colspan: 2, align: center"]Min. Cf is = to or > 200 Cf[/TD]
[/TR]
[TR]
[TD="class: xl80, width: 93, align: center"][/TD]
[TD="class: xl75, width: 96, align: center"][/TD]
[TD="class: xl76, align: center"]#DIV /0![/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71, align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl80, width: 93, align: center"][/TD]
[TD="class: xl75, width: 96, align: center"][/TD]
[TD="class: xl76, align: center"]#DIV /0![/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71, align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Edit: I hadn't seen the last post so was answering in relation to the original formula/layout

Try this cleaned-up version

=IF(OR(I3<>"YES",A3="",C3="",C3="WG"),"",
IF(C3="GE",F3/A3,IF(OR(C3="H",C3="SR"),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"),F3/1.1/A3,
IF(OR(C3="DT",C3="X"),F3/1.12/A3,
IF(OR(C3="MD",C3="MN",C3="NO"),F3/1.13/A3))))))

 

[jgold20]

No idea how to use IFERROR in my formula with the combination of IF/OR/AND statements. This would seem like the easiest way to do it.