IF OR NEST ERROR

[clh_1496]

Hi,

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 · Accepted solution]

@clh_1496 

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

[#3]

@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"))