Nested IF Formula Help

Bridgett_S

I have two Nested IF formulas that work separately, but keep given me an "incorrect argument set"  error when I combine them.  The two formulas are:

=IF(OR([Current Status]1 = "Completed", [Current Status]1 = "Terminated"), "Blue", IF(AND(OR([Current Status]1 = "Planned", [Current Status]1 = "Allocated", [Current Status]1 = "On-Hold"), [Estimated Initiation Date]1 < TODAY()), "Red"))

=IF([Current Status]2 = "Active", "Green", IF(AND(OR([Current Status]2 = "Allocated", [Current Status]2 = "Planned", [Current Status]2 = "On-Hold"), [Estimated Initiation Date]2 < TODAY() + 30), "Yellow"))

The combined formula is:

=IF(OR([Current Status]1 = "Completed", [Current Status]1 = "Terminated"), "Blue", IF(AND(OR([Current Status]1 = "Planned", [Current Status]1 = "Allocated", [Current Status]1 = "On-Hold"), [Estimated Initiation Date]1 < TODAY()), "Red"), IF([Current Status]1 = "Active", "Green", IF(AND(OR([Current Status]1 = "Allocated", [Current Status]1 = "Planned", [Current Status]1 = "On-Hold"), [Estimated Initiation Date]1 < TODAY() + 30), "Yellow")))

The same error appears when I try:

=IF(OR([Current Status]1 = "Completed", [Current Status]1 = "Terminated"), "Blue", IF(AND(OR([Current Status]1 = "Planned", [Current Status]1 = "Allocated", [Current Status]1 = "On-Hold"), [Estimated Initiation Date]1 < TODAY()), "Red"), IF([Current Status]1 = "Active", "Green"))

I'd appreciate any help in resolving this issue!

Nic Larsen

I wasn't seeing exactly what was wrong in the formula but I typed it out and this seems to be working. I hope I didn't leave any pieces out. There is likely a simplified cleaner way of doing it but give this a try:

=IF([Current Status]@row = "Active", "Green", IF(OR([Current Status]@row = "Completed", [Current Status]@row = "Terminated"), "Blue", IF(AND(OR([Current Status]@row = "Planned", [Current Status]@row = "Allocated", [Current Status]@row = "On-Hold"), [Estimated Initiation Date]@row < TODAY()), "Red", IF(AND(OR([Current Status]@row = "Allocated", [Current Status]@row = "Planned", [Current Status]@row = "On-Hold"), [Estimated Initiation Date]@row < TODAY() + 30), "Yellow"))))

Paul Newcome

It looks like you may have a parenthesis out of place. Try moving the parenthesis after "Red" to the end of the formula...

.

=IF(OR([Current Status]1 = "Completed", [Current Status]1 = "Terminated"), "Blue", IF(AND(OR([Current Status]1 = "Planned", [Current Status]1 = "Allocated", [Current Status]1 = "On-Hold"), [Estimated Initiation Date]1 < TODAY()), "Red"), IF([Current Status]1 = "Active", "Green", IF(AND(OR([Current Status]1 = "Allocated", [Current Status]1 = "Planned", [Current Status]1 = "On-Hold"), [Estimated Initiation Date]1 < TODAY() + 30), "Yellow")))

.

Turns into

.

=IF(OR([Current Status]1 = "Completed", [Current Status]1 = "Terminated"), "Blue", IF(AND(OR([Current Status]1 = "Planned", [Current Status]1 = "Allocated", [Current Status]1 = "On-Hold"), [Estimated Initiation Date]1 < TODAY()), "Red", IF([Current Status]1 = "Active", "Green", IF(AND(OR([Current Status]1 = "Allocated", [Current Status]1 = "Planned", [Current Status]1 = "On-Hold"), [Estimated Initiation Date]1 < TODAY() + 30), "Yellow"))))

Bridgett_S

Thanks, it's working now!