Forum Discussion

clh_1496's avatar
clh_1496
Copper Contributor
Dec 13, 2022
Solved

IF OR NEST ERROR

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?

 

excel
Formulas and Functions
office 365

2 Replies

Sort By
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Dec 13, 2022

      HansVogelaar 

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

Resources