0

This is a second follow up question from excel-2013-too-many-arguments-message-to-ifand-formula

Hello -

I am still working on my decision form and ran into another problem. This is my formula:

=+IF($J7="Planned - Shift / Vacation Coverage",IF(O7="No","Is Helper out during day shift?","Is an Eng out during day shift?"),
IF($J7="Planned - Off-shift PMs",IF(O7="No","Schedules OT - STOP","LE schedules work within shift - STOP"),
IF($J7="Planned - Training",IF(O7="No","Schedules training during shift - STOP","Schedules OT - STOP"),
IF($J7="Unplanned - Sick call in",IF(O7="No","Is Helper out during day shift?","Is an Eng out during day shift?"),
IF($J7="Unplanned - Emergency response/repair", IF(N7="Can this be handled by scheduled resources within shift?", IF(O7="No","Is the request a code 2?","Work is executed within shift - STOP"), IF(N7="Is the request a code 3?", IF(O7="No", "Schedules OT - STOP","Can this be handled by scheduled resources within shift?"),
IF($J7="Unplanned - Weather",IF(N7="Can resources be allocated from the shift in the first instance?",IF(O7="No","Schedules OT - STOP","CE assigns work to LE - STOP"), IF(N7="Can CE or ACE become the additional HC?",IF(O7="No","Schedules OT - STOP","CE or ACE covers shift - STOP")))))))))))

I am getting the expected outcome for each of my OT types with the exception of the last one – “Unplanned – Weather”. It is a binary IF formula, similar to the one above “Unplanned – Emergency response/repair”, but for some reason, this one is working but the former one is not. I get a "FALSE" output for both responses.

Community
  • 1
  • 1
Sonia
  • 9
  • 4
  • on a side note you don't need that + out in front right after the = sign. I do it frequently as a nice way to start a formula but in reality it does nothing for you in the end. Its removal while "tiddy" is not the problem you are facing. We will get to that in a moment. – Forward Ed May 18 '16 at 19:33
  • so for starters I will suggest you look at the part of your formula that is not working by breaking it out. `=IF($J7="Unplanned - Weather", IF(N7="Can resources be allocated from the shift in the first instance?", IF(O7="No","Schedules OT - STOP","CE assigns work to LE - STOP"), IF(N7="Can CE or ACE become the additional HC?", IF(O7="No","Schedules OT - STOP","CE or ACE covers shift - STOP"))))` Lets see if we can spot the problem in there. – Forward Ed May 18 '16 at 19:43
  • When it is giving you unexpected results, what are your values for J7, O7, and N7? To save on some typing, you can replace those sentences with AAA, BBB, CCC etc. until you get it working. one of the problems you may be facing is a typing error in your input values since they are so long. A simple space after the last character or before the first is enough to throw things off. – Forward Ed May 18 '16 at 19:46

2 Answers2

0
=IF($J7="Unplanned - Weather", IF(N7="Can resources be allocated from the shift in the first instance?", IF(O7="No","Schedules OT - STOP","CE assigns work to LE - STOP"), IF(N7="Can CE or ACE become the additional HC?", IF(O7="No","Schedules OT - STOP","CE or ACE covers shift - STOP"))))

Let look at what happens when J7 is equal Unplanned -Weather? It mean our first ifstatement is true. And we move on to:

IF(N7="Can resources be allocated from the shift in the first instance?", IF(O7="No","Schedules OT - STOP","CE assigns work to LE - STOP"), IF(N7="Can CE or ACE become the additional HC?", IF(O7="No","Schedules OT - STOP","CE or ACE covers shift - STOP")))

Just noticed that If J7 was not equal to unplanned - weather, then we have not told the if statement what to do. What happens if J7 was equal to STACK. Since you did not provide a false result for the J7 check, by default it will return false.

Now back to what happens if J7 check comes out true and we are dealing now with an N7 check. Lets pretend N7 is equal to QBERT. So the fist N7 check is false and we are now looking at:

IF(N7="Can CE or ACE become the additional HC?", IF(O7="No","Schedules OT - STOP","CE or ACE covers shift - STOP"))

N7 is still equal to QBERT so it is once again false for the N7 check which means...Hey there is no option for checking N7, so that means by default Excel will return False. Why dont we toss in an error catching statement her to let us no there is a problem with N7. So the last formula becomes:

IF(N7="Can CE or ACE become the additional HC?", IF(O7="No","Schedules OT - STOP","CE or ACE covers shift - STOP"),"N7 check is FALSE")

So if we roll that all back up into the original equation and add an error check for when J7 fails too, then we should wind up with something like:

=IF($J7="Unplanned - Weather",IF(N7="Can resources be allocated from the shift in the first instance?",IF(O7="No","Schedules OT - STOP","CE assigns work to LE - STOP"), IF(N7="Can CE or ACE become the additional HC?",IF(O7="No","Schedules OT - STOP","CE or ACE covers shift - STOP"),"N7 is False")),"J7 is Flase")

See if that part helps you figure out where the problem is. Remember this is just looking at the problem when J7 is supposed to be equal to unplanned - weather and not when J7 is one of your other planned J7 values.

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
0

working on such depth of IF condition is possible but often impractical. you may want to reconsider your structure.

one way is to build a condition table where you list down all possible conditions such as shown in table below. The 'joined condition' column is for concatenating condition "J","N" and "O" as text delimited by "|". And last column is the mapping outcome you want to return.

(the 'Joined condition' column act as if it is the AND operator, with formula =CONCATENATE([@[Condition "J"]],"|",[@[Condition "N"]],"|",[@[Condition "O"]])).

enter image description here

Then in your main data table, simple apply VLOOKUP, such as below:

enter image description here

This keep formula short and easy maintain. For the condition table, you will have easy control on how you want to change the outcome in the future. Mean time, add and delete of outcomes will also be much easier.

Rosetta
  • 2,665
  • 1
  • 13
  • 29