Combining MID, LEN, and ABS, all within an IF statement

[rfernandes]

I have a really long string of nested if statement:

=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",MID(D8,ABS(LEN(D8)-2),2)="SR",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",MID(D8,LEN(D8)-2,2)="SR",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",MID(D8,ABS(LEN(D8)-2),2)="SR",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","")))))))))))))))))))))))))))

The only part of it that is not working is highlighted in red. When D8=0, this causes a #VALUE error, when what I really want is for it to be FALSE and go on looking until it references cell D15 (the last five strings of if conditions.)

My theory was that in using the ABS function, I would at least get rid of the negative number that results but this still produces the #VALUE error.

What am I doing wrong guys?


-Becky

[daddylonglegs]

=MID(D8,LEN(D8)-2,2) gives you the first 2 of the last 3 characters of D2 so instead of using

MID(D8,LEN(D8)-2,2)="SR"

try using COUNTIF instead, i.e.

COUNTIF(D2,"*SR?")=1

That will give you TRUE/FALSE depending on whether "SR" occurs in that position or not.....without any errors

[rfernandes]

I've realized I am a complete idiot.. I can use both options (yours and mine). The key is that I should have been applying it to all of those strings (there are three MID functions that include that "SR" requirement).


Thanks daddylonglegs!