Help nesting multiple if's

[ddub25]

=IF(E33="IB",
IF(AND(N33="Single",T33="W",OR(J33={"BF","BD"})),SUM((M33*R33)-(((M33-1)*R33)*S33)),
IF(AND(N33="Single E",T33="W",OR(J33={"BF","BD"})),SUM((R33/2)*M33)-(((M33-1)*(R33/2)*S33))+SUM(SUM(((M33-1)*25%)+1)*(R33/2))-((((M33-1)*25%)*(R33/2)*S33)),
IF(AND(N33="Single E",T33="P",OR(J33={"BF","BD"})),SUM(R33/2)*(((M33-1)*25%)+1)-((((M33-1)*25%)+1)*(R33/2)-(R33/2))*S33,
IF(AND(N33="Single",T33="W"),SUM(M33*R33),
IF(AND(N33="Single E",T33="W"),SUM((R33/2)*M33)+SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single E",T33="P"),SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single",T33="L"),0,
IF(AND(N33="Single E",T33="L"),0,
IF(AND(N33="Single",T33="V"),R33,
IF(AND(N33="Single E",T33="V"),R33,
"Error")))))))))),"Error")

=IF(E33="FB",
IF(AND(N33="Single",T33="W",OR(J33={"BF","BD"})),SUM((M33*R33)-(((M33-1)*R33)*S33)),
IF(AND(N33="Single E",T33="W",OR(J33={"BF","BD"})),SUM((R33/2)*M33)-(((M33-1)*(R33/2)*S33))+SUM(SUM(((M33-1)*25%)+1)*(R33/2))-((((M33-1)*25%)*(R33/2)*S33)),
IF(AND(N33="Single E",T33="P",OR(J33={"BF","BD"})),SUM(R33/2)*(((M33-1)*25%)+1)-((((M33-1)*25%)+1)*(R33/2)-(R33/2))*S33,
IF(AND(N33="Single",T33="W"),SUM(M33*R33),
IF(AND(N33="Single E",T33="W"),SUM((R33/2)*M33)+SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single E",T33="P"),SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single",T33="L"),0,
IF(AND(N33="Single E",T33="L"),0,
IF(AND(N33="Single",T33="V"),R33,
IF(AND(N33="Single E",T33="V"),R33,
"Error")))))))))),"Error")

I have these two formulas that I'd like to put together. The first has multiple if statements nested inside =IF(E33="IB",
The second has if's nested inside =IF(E33="FB",
When I try and put them together as one formula it returns '#VALUE!'
Can anyone help with the correct syntax?
(Note: this is just an example as you'll notice that all the nested if's are the same in both formulas - these will be different. I am just trying to understand the syntax required for now).

Dan

 

[Jugantarji]

Hi Dan

You can only have 7 If conditons in A nested If formula. Assuming you have your formulas in A1 and B1 you could replace something at the end of the formula where you have "Error" and instead put b1 to refer to B1

 

[Andrew Poulsom]

Is it?

=IF(E33="IB", IF(AND(N33="Single",T33="W",OR(J33={"BF","BD"})),SUM((M33*R33)-(((M33-1)*R33)*S33)), IF(AND(N33="Single E",T33="W",OR(J33={"BF","BD"})),SUM((R33/2)*M33)-(((M33-1)*(R33/2)*S33))+SUM(SUM(((M33-1)*25%)+1)*(R33/2))-((((M33-1)*25%)*(R33/2)*S33)), IF(AND(N33="Single E",T33="P",OR(J33={"BF","BD"})),SUM(R33/2)*(((M33-1)*25%)+1)-((((M33-1)*25%)+1)*(R33/2)-(R33/2))*S33, IF(AND(N33="Single",T33="W"),SUM(M33*R33), IF(AND(N33="Single E",T33="W"),SUM((R33/2)*M33)+SUM(SUM(((M33-1)*25%)+1)*(R33/2)), IF(AND(N33="Single E",T33="P"),SUM(SUM(((M33-1)*25%)+1)*(R33/2)), IF(AND(N33="Single",T33="L"),0, IF(AND(N33="Single E",T33="L"),0, IF(AND(N33="Single",T33="V"),R33, IF(AND(N33="Single E",T33="V"),R33, "Error")))))))))),IF(E33="IB", IF(AND(N33="Single",T33="W",OR(J33={"BF","BD"})),SUM((M33*R33)-(((M33-1)*R33)*S33)), IF(AND(N33="Single E",T33="W",OR(J33={"BF","BD"})),SUM((R33/2)*M33)-(((M33-1)*(R33/2)*S33))+SUM(SUM(((M33-1)*25%)+1)*(R33/2))-((((M33-1)*25%)*(R33/2)*S33)), IF(AND(N33="Single E",T33="P",OR(J33={"BF","BD"})),SUM(R33/2)*(((M33-1)*25%)+1)-((((M33-1)*25%)+1)*(R33/2)-(R33/2))*S33, IF(AND(N33="Single",T33="W"),SUM(M33*R33), IF(AND(N33="Single E",T33="W"),SUM((R33/2)*M33)+SUM(SUM(((M33-1)*25%)+1)*(R33/2)), IF(AND(N33="Single E",T33="P"),SUM(SUM(((M33-1)*25%)+1)*(R33/2)), IF(AND(N33="Single",T33="L"),0, IF(AND(N33="Single E",T33="L"),0, IF(AND(N33="Single",T33="V"),R33, IF(AND(N33="Single E",T33="V"),R33, "Error")))))))))),"Error"))

There must be a better way of getting a result than using IF.

 

[Jugantarji]

Sorry just seen that they're identical apart from the first argument, try

=IF(OR(E33="FB",E33="IB"),
IF(AND(N33="Single",T33="W",OR(J33={"BF","BD"})),SUM((M33*R33)-(((M33-1)*R33)*S33)),
IF(AND(N33="Single E",T33="W",OR(J33={"BF","BD"})),SUM((R33/2)*M33)-(((M33-1)*(R33/2)*S33))+SUM(SUM(((M33-1)*25%)+1)*(R33/2))-((((M33-1)*25%)*(R33/2)*S33)),
IF(AND(N33="Single E",T33="P",OR(J33={"BF","BD"})),SUM(R33/2)*(((M33-1)*25%)+1)-((((M33-1)*25%)+1)*(R33/2)-(R33/2))*S33,
IF(AND(N33="Single",T33="W"),SUM(M33*R33),
IF(AND(N33="Single E",T33="W"),SUM((R33/2)*M33)+SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single E",T33="P"),SUM(SUM(((M33-1)*25%)+1)*(R33/2)),
IF(AND(N33="Single",T33="L"),0,
IF(AND(N33="Single E",T33="L"),0,
IF(AND(N33="Single",T33="V"),R33,
IF(AND(N33="Single E",T33="V"),R33,
"Error")))))))))),"Error")

 

[ddub25]

Thanks Andrew, that's what I was after, just couldn't get the syntax right. How else can I ask these questions other than using IF?