HELP writing a multi-variable IF Formula

Balle053

Looking for some help with automating a Health column with Red, Yellow, Gray, Green balls based on 4 corresponding values in a Status drop-down. But there are also some overrides that I want to incorporate and can't figure out.

Back Log = Gray

In Progress = Yellow

Flagged = Red

Complete = Yellow

 

Override 1

Marking something "Complete" triggers an approval workflow tracked in another drop-down column. The dropdown options are Submitted, Approved, Declined. 

[Status] "Any" + [Approval] "Submitted" or "Declined" = no symbol change 

[Status] "Complete" + [Approval] "Approved" = Green

Override 2

If any task is near its deadline it is automatically marked red, unless it has a full approval.

[Status] "Any" + [Deadline] is past or is less than 3 days away = Red

[Status] "Complete" + [Approval] "Approved" + [Deadline] is past or less than 3 days away = Green

 

Any help would be welcome! I realize its a complicated formula, but I really want to be able to pull a "Health" metric that covers multiple scenarios 

Genevieve P.

Nested IF formulas are logic formulas. This means that the formula looks at each individual instruction in the order that they were written and stops as soon as a scenario is found, without reading the rest of the formula. In order to "override" certain instructions in an IF formula you just need to make sure you're placing each instruction in the right order.

In this instance, you will want your first instruction to be to see if the Status is "Complete" and the Approved column is also "Approved", then the health colour will be Green. If you have this first and then follow with your instruction to look if it's near the deadline, the deadline instruction will automatically only be looked at if that Status is not Complete and Approved is not Approved.

The order of your formula should be as follows:

 

If Status is "Complete" and Approved is "Approved", return Green.

If the Deadline is within 3 days or in the past, return Red.

Otherwise,

If the status is "Back Log", return Gray

If the status is "In Progress", return Yellow

If the status is "Flagged", return Red

If the status is "Complete", return Yellow

 

 

Here's an example of what this will look like written out in a nested IF formula:

 

=IF(AND(Status@row = "Complete", Approved@row = "Approved"), "Green", IF([Due Date]@row < TODAY(3), "Red", IF(Status@row = "Back Log", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Flagged", "Red", IF(Status@row = "Complete", "Yellow"))))))

You can read more about the IF Function, here.