Nested If Statements

[Tennisguuy]

I am having a problem with a nested if statement. I have a situation where if the first three digits in cell E38 of a value equal a certain number and or if the value in cell F38 or G38 are less than a value I wanted a statement to appear. This is the formula I used in cell H38

IF(E38="","",IF(AND(F38="",G38=""),"",IF(OR(LEFT(E38,3)=211,LEFT(E38,3)=212,LEFT(E38,3)=213,LEFT(E38,3)=214,LEFT(E38,3)=215,LEFT(E38,3)=216,LEFT(E38,3)=231,LEFT(E38,3)=232,LEFT(E38,3)=233,LEFT(E38,3)=234,LEFT(E38,3)=235,LEFT(E38,3)=236,AND(OR(F38<1000,G38<1000))),"MINIMUM DED IS 1,000/1,000",IF(OR(LEFT(E38,3)=311,LEFT(E38,3)=312,LEFT(E38,3)=313,LEFT(E38,3)=314,LEFT(E38,3)=315,LEFT(E38,3)=316,LEFT(E38,3)=331,LEFT(E38,3)=332,LEFT(E38,3)=333,LEFT(E38,3)=334,LEFT(E38,3)=335,LEFT(E38,3)=336,AND(OR(F38<2000,G38<2000))),"MINIMUM DED IS 2,000/2,000",IF(OR(LEFT(E38,3)=401,LEFT(E38,3)=402,LEFT(E38,3)=403,LEFT(E38,3)=404,LEFT(E38,3)=405,LEFT(E38,3)=406,AND(OR(F38<3000,G38<3000))),"MINIMUM DED IS 3,000/3,000",IF(OR(LEFT(E38,3)=501,LEFT(E38,3)=502,LEFT(E38,3)=503,LEFT(E38,3)=504,LEFT(E38,3)=505,LEFT(E38,3)=506,AND(OR(F38<3000,G38<3000))),"MINIMUM DED IS 3,000/3,000",IF(AND(F38<500,G38<500),"MINIMUM DED IS 500/500","")))))))

For example if the value in cell E38 is 21499 and the value in cell F38 is 500 and the value in cell G38 is 1,000 I wanted the statement minimum ded is 1000/1000 since one of the values in cell F38 or G38 is less than 1000. When I enter those value I get the correct statement. However, if I just changed the value in cell E38 to 31499 and don't change the values in cell F38 or G38 the same statement minimum deductible is 1000/1000 and the statement that should appear is minimum deductible is 2000/2000. However if I change the value in cell F38 from 500 to 1000 the correct statement appears. If I change the value in cell E38 to 7389 and change the value in cell F38 to 250 and value in cell G38 to 500. The statement minimum ded is 1,000/1,000 appears and the correct statement should be minimum deductible is 500/500.

I tried different variations to get the formula to work and in some situation it would but in others it wouldn't.

Is my setup incorrect.

 

[Tennisguuy]

Thanks Alan that worked. Really really appreciate it

 

[AlanY]

you're welcome