Hi Guys,
I have a long string of nested if statements:
=IF(OR(A17="LIMIT SWITCHES:",A17="SOLENOID:",A17="AIR SETS:",A17="POSITIONERS:",A17="MANUAL OVERRIDE:",A17=""),"",IF(AND(LEFT(D8,1)="G",OR(MID(D8,3,1)="7",MID(D8,3,1)="8",MID(D8,2,2)="10",MID(D8,2,2)="13")),"Please Advise Below",IF(AND(LEFT(D8,1)="G",OR(MID(D8,3,1)="1",D8,3,1)="2"),"1/2""",IF(AND(LEFT(D8,1)="G",SEARCH("SR",D8,1),MID(D8,3,1)="3"),"1/2""",IF(AND(LEFT(D8,1)="G",MID(D8,3,1)="3"),"5/8""",IF(AND(LEFT(D8,1)="G",SEARCH("SR",D8,1),MID(D8,3,1)="4"),"5/8""",IF(AND(LEFT(D8,1)="G",MID(D8,3,1)="4"),"3/4""",IF(AND(LEFT(D8,1)="G",SEARCH("SR",D8,1),MID(D8,3,1)="5"),"3/4""",IF(AND(LEFT(D8,2)="GC",MID(D8,3,1)="5"),"Please Advise Below",IF(AND(LEFT(D8,2)="G0",MID(D8,3,1)="5"),"3/4""",IF(OR(MID(D8,3,2)="00",MID(D8,4,1)="1"),"1/4''",IF(AND(OR(LEFT(D8,2)="ES",LEFT(D8,2)="ED",LEFT(D8,2)="DD",LEFT(D8,2)="QD"),(MID(D8,4,1)="2"),D11>=120),"3/8''",IF(AND(OR(LEFT(D8,2)="DS",LEFT(D8,2)="QS",LEFT(D8,2)="ES", LEFT(D8,2)="ED",LEFT(D8,2)="DD",LEFT(D8,2)="QD"),(MID(D8,4,1)="2")),"1/4''",IF(AND(OR(LEFT(D8,2)="DS",LEFT(D8,2)="QS"),(MID(D8,4,1)="3")),"3/8''",IF(AND(OR(LEFT(D8,2)="ED",LEFT(D8,2)="DD",LEFT(D8,2)="QD",LEFT(D8,2)="ES"),(MID(D8,4,1)="3")),"1/2""",IF(OR(MID(D8,4,1)="6",(MID(D8,4,1)="9"),MID(D8,3,1)="1",MID(D8,3,1)="2",MID(D8,3,1)="4"),"1/2""",IF(AND(LEFT(D8,3)="CBA",MID(D8,5,1)="3"),"1/2""",IF(AND(LEFT(D8,3)="CBA",MID(D8,5,1)="1"),"1/4""",IF(AND(LEFT(D8,3)="CBA",MID(D8,5,2)="20"),"3/8""",IF(AND(LEFT(D8,3)="CBA",MID(D8,5,2)="25"),"1/2""",IF(D15="","",IF(D15<3000,"1/4""",IF(AND(D15>3000,D15<5000),"3/8''",IF(AND(D15>5000,D15<90000),"1/2""",IF(AND(D15>90000,D15<180000),"5/8""",IF(AND(D15>180000,D15<500000),"3/4""",IF(D15>500000,"Please Advise Below","")))))))))))))))))))))))))))
What I'm having a problem with is this guy:
SEARCH("SR",D8,1)
When this is TRUE, we're a-ok, but when it's FALSE, instead of producing FALSE, as I had envisioned, it produces a #VALUE error. And when I get a #VALUE, the rest of the if statements aren't evaluated.
What am I doing wrong guys?!
-Becky
Bookmarks