My nested IF OR statement isn't working and I can't work out why. Basically column K either has Unfit or Part SAT, and column L has Available or Constraint, and when changed to different combos it basically does a calculation to more time to the budgeted hours of 18. 



I have the following formula:


=IF(OR(K9="Part SAT",L9="Constraint"),D9*2,IF(OR(K9="Unfit",L9="Constraint"),D9*3,IF(OR(K9="Unfit",L9="Available"),D9*2,IF(OR(K9="Part SAT",L9="Available"),D9,""))))


But if you look at K and L columns, I've selected Unfit and Available so based on the formula, it should show 36 (D9*2) in Column E but it consistently shows 54 (D9*3) instead?




Does anyone know why formula isn't working - I had thought it was because Column K initially had UNSAT & SAT as options so I've changed this to SAT & Unfit and its still not working?


2 Replies
best response confirmed by clh_1496 (Contributor)


You need to use AND instead of OR, as far as I can tell.

@Hans Vogelaar 

That was my conclusion as well.

The formula is a bit shorter with a nested IF within each branch of the first condition

= D9 * 
     IF(K9="Part SAT", 
        IF(L9="Constraint", 2, 1), 
        IF(L9="Constraint", 3, 2)

or, then again, one could dispense with the IFs and use

= D9 * (1 + (K9="Unfit") + (L9="Constraint"))