 # Multiple Nested If Formulas: Greater Than 7

• Hi all i have a nested worksheet formula as below in box 2 (took some time to devise it!!!), the formula works fine but i need to add the line in box 1 to the statement, but i have read that there is a limit of 7 IF's to a statement anyone know a clever way around it?
Box1:

Code
``=If(And(N5=4,K2="Line Off"),"",If(And(F5=4,K2="Line Off"),"",If(And(B5=4,K2="Line Off"),"",If(And(R5=4,K2="Line Off"),"",If(And(V5=4,K2="Line Off"),""``

Box2:

Code
``=If(And(N5=4,K2="No Ops"),"",If(And(F5=4,K2="No Ops"),"",If(And(B5=4,K2="No Ops"),"",If(And(R5=4,K2="No Ops"),"",If(And(V5=4,K2="No Ops"),"",If(K2="Line Off",B33,If(K2="No Ops",B33)))))))``

Hope you can help!

Regards,
Simon

• Re: Multiple If Statements In Worksheet Formula?

A nice fellow called Matt at another forum gave me this

Code
``=IF(K2="No Ops",IF(OR(N5=4,F5=4,B5=4,R5=4,V5=4),"",B33),IF(K2="Line Off",B33,"meets no conditions"))``

which showed me the way so i expanded and came up with this

Code
``=If(G2="No Ops",If(Or(N5=3,J5=3,B5=3,R5=3,V5=3),"",B32),If(G2="Line Off",If(Or(N5=3,J5=3,B5=3,R5=3,V5=3),"",B32),If(G2="Line Off",B32,"")))``

which works perfect!

to anyone who has spent any time looking at this, i thank you....hope the solution above helps you too.

regards,
Simon

• Re: Multiple If Statements In Worksheet Formula?

For people like me who can't take in formulas in VBA boxes, here are the 4 formulas above:

=If(And(N5=4,K2="Line Off"),"",If(And(F5=4,K2="Line Off"),"",If(And(B5=4,K2="Line Off"),"",If(And(R5=4,K2="Line Off"),"",If(And(V5=4,K2="Line Off"),""

=If(And(N5=4,K2="No Ops"),"",If(And(F5=4,K2="No Ops"),"",If(And(B5=4,K2="No Ops"),"",If(And(R5=4,K2="No Ops"),"",If(And(V5=4,K2="No Ops"),"",If(K2="Line Off",B33,If(K2="No Ops",B33)))))))

=If(K2="No Ops",If(Or(N5=4,F5=4,B5=4,R5=4,V5=4),"",B33),If(K2="Line Off",B33,"meets no conditions"))

=If(G2="No Ops",If(Or(N5=3,J5=3,B5=3,R5=3,V5=3),"",B32),If(G2="Line Off",If(Or(N5=3,J5=3,B5=3,R5=3,V5=3),"",B32),If(G2="Line Off",B32,"")))

You got many more of these Simon? I did one a couple of days back. :shocked:

• Re: Multiple If Statements In Worksheet Formula?

Quote

I did one a couple of days back

ByTheCringe....yes you did and i thank you for that without your help i would still be at the starting gate,

Quote

You got many more of these Simon?

the answer to your question is simply ....."I don't know" as i am developing a crewing sheet and everytime i come to work on it i find that i either need some extra fuctionality or come across a "Wouldn't it be nice if..." scenario.

Sorry about the vba boxes but didnt want to get told off for just letting them appear in type!

Hope i didnt offend you in obtaining the answer elsewhere (i kind of needed the answer now so was hedging my bets!)

Regards,
Simon

• Re: Multiple If Statements In Worksheet Formula?

Hi Simon,
You won't get told off. I just can't see my way round a formula in VBA boxes.

Quote

Hope i didnt offend you in obtaining the answer elsewhere (i kind of needed the answer now so was hedging my bets!)

Good Lord, no! The whole point of this forum is to help people do their own thing. If you can fix it, that's great! ## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!