Trouble with a nested if statement

[easpeed954]

I can't get a nested if statement to work. Using Excel 2010 on Windows 10

This my current working formula:
=IF(Q4="SML STND",2.41,
IF(Q4="LRG STND",IF(R4<=1,3.19,IF(R4<=2,4.71,4.71+(ROUNDUP(R4,0)-2)*0.38)),
IF(Q4="SPL OVER",IF(R4<=90,137.32,137.32+(ROUNDUP(R4,0)-90)*0.91),
IF(Q4="LRG OVER",IF(R4<=90,75.78,75.78+(ROUNDUP(R4,0)-90)*0.79),
IF(Q4="MED OVER",IF(R4<=2,9.79,9.79+(ROUNDUP(R4,0)-2)*0.39),
IF(Q4="SML OVER",IF(R4<=2,8.26,8.26+(ROUNDUP(R4,0)-2)*0.38)))))))

I need to update the first two sections. Separately I am able to get each of compute correctly on their own:

=IF(Q4="SML STND",IF(R4<=0.625,2.41,IF(AND(R4<1,R4>0.625),2.48)))

=IF(Q4="LRG STND",IF(R4<=0.625,3.19,IF(AND(R4<1,R4>0.625),3.28,IF(AND(R4<2,R4>1),4.76,IF(AND(R4<3,R4>2),5.26,IF(AND(R4<20,R4>3),5.09+(ROUNDUP(R4,0)-3)*0.38))))))

but when I try to stitch them together like this it don't work:
=IF(Q4="SML STND",IF(R4<=0.625,2.41,IF(AND(R4<1,R4>0.625),2.48))),
IF(Q4="LRG STND",IF(R4<=0.625,3.19,IF(AND(R4<1,R4>0.625),3.28,IF(AND(R4<2,R4>1),4.76,IF(AND(R4<3,R4>2),5.26,IF(AND(R4<20,R4>3),5.09+(ROUNDUP(R4,0)-3)*0.38)))))),
IF(Q4="SPL OVER",IF(R4<=90,137.32,137.32+(ROUNDUP(R4,0)-90)*0.91),
IF(Q4="LRG OVER",IF(R4<=90,75.78,75.78+(ROUNDUP(R4,0)-90)*0.79),
IF(Q4="MED OVER",IF(R4<=2,9.79,9.79+(ROUNDUP(R4,0)-2)*0.39),
IF(Q4="SML OVER",IF(R4<=2,8.26,8.26+(ROUNDUP(R4,0)-2)*0.38)))))))

Because each portion works independently I suspect it's just an error in the bracketing or something.

Any ideas are appreciated.

 

[mrshl9898]

Without looking into it too deeply, does this work?


Edit:

=IF(Q4="SML STND",IF(R4<=0.625,2.41,IF(AND(R4<1,R4>0.625),2.48,
IF(Q4="LRG STND",IF(R4<=0.625,3.19,IF(AND(R4<1,R4>0.625),3.28,IF(AND(R4<2,R4>1),4.76,IF(AND(R4<3,R4>2),5.26,IF(AND(R4<20,R4>3),5.09+(ROUNDUP(R4,0)-3)*0.38,
IF(Q4="SPL OVER",IF(R4<=90,137.32,137.32+(ROUNDUP(R4,0)-90)*0.91),
IF(Q4="LRG OVER",IF(R4<=90,75.78,75.78+(ROUNDUP(R4,0)-90)*0.79),
IF(Q4="MED OVER",IF(R4<=2,9.79,9.79+(ROUNDUP(R4,0)-2)*0.39),
IF(Q4="SML OVER",IF(R4<=2,8.26,8.26+(ROUNDUP(R4,0)-2)*0.38))))))))))))))

 

[easpeed954]

No, that didn't do it. What that did do was make it work for matches on "SML STND" but the others are still erroring out. Seems to be on the right track though.

Thanks for trying.

 

[mrshl9898]

Apologies, this looks to be working, though there's no False supplied for SML STAND > 1 and LRG STAND >20

=IF(Q4="SML STND",IF(R4<=0.625,2.41,IF(AND(R4<1,R4>0.625),2.48,"")),
IF(Q4="LRG STND",IF(R4<=0.625,3.19,IF(AND(R4<1,R4>0.625),3.28,IF(AND(R4<2,R4>1),4.76,IF(AND(R4<3,R4>2),5.26,IF(AND(R4<20,R4>3),5.09+(ROUNDUP(R4,0)-3)*0.38,""))))),
IF(Q4="SPL OVER",IF(R4<=90,137.32,137.32+(ROUNDUP(R4,0)-90)*0.91),
IF(Q4="LRG OVER",IF(R4<=90,75.78,75.78+(ROUNDUP(R4,0)-90)*0.79),
IF(Q4="MED OVER",IF(R4<=2,9.79,9.79+(ROUNDUP(R4,0)-2)*0.39),
IF(Q4="SML OVER",IF(R4<=2,8.26,8.26+(ROUNDUP(R4,0)-2)*0.38),""))))))