Formula Breakdown

Melissa Larkin

Hi! I got the health status formula below from a WBS template I found on a Smartsheet community thread (I can't find the link to the template but will continue to look). Anyways, can someone break the formula down into layman terms of what it's saying?

=IFERROR((IF(OR(Start@row = "", Start@row > TODAY()), "Blue", IF(OR([% Complete]@row = 1, Finish@row > TODAY(+[Task Duration Health Workdays]#)), "Green", IF(Finish@row < TODAY(), "Red", IF(NETWORKDAYS(TODAY(), Finish@row) < [Task Duration Health Workdays]#, "Yellow", "Green"))))), "")

Jeff Reisman

@Melissa Larkin Sure:

"If the Start column on this row is blank OR is greater than today's date, set the symbol to blue; otherwise, if the % Complete column on this row is 1 (100%), OR the Finish column on this row is greater than today's date + the value in the Task Duration Health Workdays summary column, set the symbol to green; otherwise, if the Finish date on this row is before today's date, set the symbol to red; otherwise, if the number of business days from today to the Finish date on this row is lower than the value in the Task Duration Health Workdays summary column,, set the symbol to yellow; otherwise, set the symbol to green. And if anything causes this formula to throw an error, replace the error message with a blank cell."

Melissa Larkin

Here's where I got the template from: https://community.smartsheet.com/discussion/77827/work-breakdown-structure-wbs-column-formula-configuration

Jeff Reisman

@Melissa Larkin Sure:

"If the Start column on this row is blank OR is greater than today's date, set the symbol to blue; otherwise, if the % Complete column on this row is 1 (100%), OR the Finish column on this row is greater than today's date + the value in the Task Duration Health Workdays summary column, set the symbol to green; otherwise, if the Finish date on this row is before today's date, set the symbol to red; otherwise, if the number of business days from today to the Finish date on this row is lower than the value in the Task Duration Health Workdays summary column,, set the symbol to yellow; otherwise, set the symbol to green. And if anything causes this formula to throw an error, replace the error message with a blank cell."

Melissa Larkin

THANK YOU SOOOOOOOOOOOO MUCH!!!!!!

Melissa Larkin

@Jeff Reisman - What does the hashtag mean in the formula? I'm trying to replicate this equation in another sheet and I'm getting stuck on the #. I don't understand how the # relates to the [Task Duration Health Workdays]. I'm trying to replicate this formula in another sheet.

TODAY(+[Task Duration Health Workdays]#)

Jeff Reisman

@Melissa Larkin Are you familiar with Sheet Summary fields? If not, here's an article on them.

When you create a summary field, you can use this field in calculations and formulas in that sheet. This is handy if say you need the same calculation in a lot of formulas but don't need to dedicate a whole column or a remote lookup sheet to it. You can reference the Sheet Summary Field using the name you gave it, followed by the # sign (where the row number or "@row" would be if referencing a cell in a regular column.)

Here's an example:

I created this summary field called Total Type A to count the number of populated cells in the TypeA column:

Then I referenced that in this formula in a regular column:

Melissa Larkin

This is great! Thank you so much. I was able to get it to work.

Now... I want to add 2 additional factors into the main equation: (1) If less than 50% of work is complete when the sum of today's date and finish date is greater than or equal to 1/2 the duration, show Yellow (aka if < 50% is complete at the halfway point or past the halfway point show yellow) and (2) If less than 25% is complete when the sum of today's date and finish date is greater than or equal to 1/2 the duration, show Red (aka if < 25% is complete at the halfway point or past the halfway point show red).

Below is the main equation and what I think the equations for the additions should be. I just don't know how to merge them all together. Any insight @Jeff Reisman? Or am I completely off? 😕

Main Equation: IFERROR((IF(OR(Start@row = "", Start@row > TODAY()), "Blue", IF(OR([% Complete]@row = 1, Finish@row > TODAY(+[Task Duration Health Workdays]#)), "Green", IF(Finish@row < TODAY(), "Red", IF(NETWORKDAYS(TODAY(), Finish@row) < [Task Duration Health Workdays]#, "Yellow", "Green"))))), "")

1st Addition to Main Equation: IF(AND([% Complete]@row <= 0.5, NETWORKDAY(TODAY(), Finish@row) <= (0.5 * Duration@row)), "Yellow")

2nd Addition to Main Equation: IF(AND([% Complete]@row <= 0.25, NETWORKDAY(TODAY(), Finish@row) <= (0.5 * Duration@row)), "Red")

Jeff Reisman

@Melissa Larkin When I build these complex IFs for status columns, I try to lay out the logic so that I can see if any criteria that I need considered are going to get ignored because the formula gets to true too early. When any IF reaches a true logical statement, it can only use the true condition, so it will never continue on to consider IFs that are further "downstream."

Here's your main formula broken into the logic steps:

IF Start date is blank OR Start date is after Today, BLUE

IF % Complete is 1 OR Finish is greater than Today's date plus the # of task duration days, GREEN

IF Finish date is before today, RED

IF # of Net work days from today to the finish date is less than the # of task duration days, YELLOW

Otherwise, GREEN

And you want to add in criteria for YELLOW and for RED... Both new criteria require the # of net work days from today to the finish date to less than or equal to the half the Duration. Red requires % complete less than or equal to .25, Yellow requires % complete less than or equal to .5.

Since the % complete being under .25 would satisfy both yellow and red, we'd want red's criteria to go before yellow's.

Now would these be required criteria in addition to the existing criteria for red and yellow? It's a question of, will RED require the finish date to be before today AND that the # of net work days from today to the finish date to be less than or equal to the half the Duration AND % complete less than or equal to .25? If so, you would just put the new criteria into RED's existing IF using AND:

IF(AND(Finish@row < TODAY(), [% Complete]@row <= 0.25, NETWORKDAY(TODAY(), Finish@row) <= (0.5 * Duration@row)), "Red",

Same thing for Yellow:

IF(AND(NETWORKDAYS(TODAY(), Finish@row) < [Task Duration Health Workdays]#, [% Complete]@row <= 0.5, NETWORKDAY(TODAY(), Finish@row) <= (0.5 * Duration@row)), "Yellow",

If the two new criteria for each color should be considered separately from the existing criteria, we'll use an OR with an AND, being sure that the AND is enclosed within the OR:

IF(OR(Finish@row < TODAY(), AND([% Complete]@row <= 0.25, NETWORKDAY(TODAY(), Finish@row) <= (0.5 * Duration@row))), "Red",

IF(OR(NETWORKDAYS(TODAY(), Finish@row) < [Task Duration Health Workdays]#, AND([% Complete]@row <= 0.5, NETWORKDAY(TODAY(), Finish@row) <= (0.5 * Duration@row))), "Yellow",

From here, just pick which of the new IFs I wrote should replace the current nested IFs for RED and YELLOW.