Forum Discussion
Nested if statements
Hi everyone I am in desperate need of help. I am pulling my hair out. I am trying to do a nested if statement with 49 if's from a drop down list and it keeps saying the formula is missing an opening ...
I can't get it to let me send the formula. What I am trying to do is have the person select from a drop down list with 49 options. Depending on which one they pick it will do a different math problem. For example if you choose the fifth one down on the list it will multiply cell j8 times 300.235.
let me see if it will allow me to do it thi=IF(G4=Sheet2!A2,J8*56.901,IF(G4=Sheet2!A3,J8*61.316,IF(G4=Sheet2!A4,J8*67.448,IF(G4=Sheet2!A5,J8*85.842,IF(G4=Sheet2!A6,J8*91.973,IF(G4=Sheet2!A7,J8*98.105,IF(G4=Sheet2!A8,J8*122.632,IF(G4=Sheet2!A10,J8*147.158,IF(G4=Sheet2!A11,J8*153.289,IF(G4=Sheet2!A13,J8*183.949,IF(G4=Sheet2!A14,J8*187.013,IF(G4=Sheet2!A15,J8*196.211,IF(G4=Sheet2!A16,J8*202.343,,IF(G4=Sheet2!A17,J8*208.474,IF(G4=Sheet2!A18,J8*233.000,IF(G4=Sheet2!A19,J8*245.265,IF(G4=Sheet2!A20,J8*282.053,IF(G4=Sheet2!A21,J8*294.316,IF(G4=Sheet2!A22,J8*374.029,IF(G4=Sheet2!A23,J8*392.423,IF(G4=Sheet2!A24,J8*42.185,IF(G4=Sheet2!A25,J8*52.732,IF(G4=Sheet2!A26,J8*84.370,IF(G4=Sheet2!A27,J8*105.464,IF(G4=Sheet2!A28,J8*126.557,IF(G4=Sheet2!A29,J8*158,195,IF(G4=Sheet2!A30,J8*168.742,IF(G4=Sheet2!A31,J8*263.659,IF(G4=Sheet2!A32,J8*28.697,IF(G4=Sheet2!A33,J8*57.391,IF(G4=Sheet2!A34,J8*71.740,IF(G4=Sheet2!A35,J8*111.962,IF(G4=Sheet2!A36,J8*152.678,IF(G4=Sheet2!A37,J8*178.123,IF(G4=Sheet2!A38,J8*103.011,IF(G4=Sheet2!A39,J8*190.324,IF(G4=Sheet2!A40,J8*93.812,IF(G4=Sheet2!A41,J8*187.627,IF(G4=Sheet2!A44,J8*156.356,IF(G4=Sheet2!A45,J8*166.780,IF(G4=Sheet2!A46,J8*208.474,IF(G4=Sheet2!A47,J8*234.533,IF(G4=Sheet2!A48,J8*239.745,IF(G4=Sheet2!A49,J8*250.169,IF(G4=Sheet2!A50,J8*317.924,IF(G4=Sheet2!A51,J8*56.901,IF(G4=Sheet2!A52,J8*98.105,IF(G4=Sheet2!A53,J8*202.343,IF(G4=Sheet2!A54,J8*245.265,)s way.
but it's always J8 times something, is that right?
In that case you can still use the suggestion from Hans Vogelaar to accomplish the desired result with far less pain. Whatever is selected from that drop down is used as a reference in the VLOOKUP with a formula like=J8 * VLOOKUP(dropdownselection,TableOfValues,2,0)
ALL OF THE TEXTBOOKS on Excel warn against trying to nest too many IFs in a single formula; it becomes (as you have discovered) utterly unwieldy, to say nothing of unintelligible, un-maintainable...etc.
