Health Check Using Nested IF with Various Criteria

Marcela Hernandez

Hello,

I am trying to created a nested IF formula and stuck. A column was created for each color to help with validation. Originally, YELLOW was set at IF([Time Left]@row >= 3), but then both YELLOW and GRAY would highlight. Note: That any value would return a yellow ball that is greater than 3, even N/A. When the value is 2 or less, the cell is blank.

Now, have updated yellow to include a range, but an error is returning with the exception of cells with values less than 3.

Below are the values that will need to be built into a single formula for a health check column.

RED: =IF([Time Left]@row = "Past Due", "Red")

YELLOW: =IF([Time Left]@row >= 3, IF(AND([Time Left]@row <= 6, "Yellow"))) - Triggering #Incorrect Argument Set

GREEN: =IF([Time Left]@row <= 3, "Green")

GRAY: =IF([Project Status]@row = "Complete", "Gray")

Appreciate your help! Muchas gracias in advance.

Paul Newcome

Try this:

=IF([Time Left]@row = "Past Due", "Red", IF([Time Left]@row = "Complete", "Gray", IF([Time Left]@row <= 3, "Green", IF([Time Left]@row <= 6, "Yellow"))))

Paul Newcome

@Marcela Hernandez You would use another IF statement to say "if the cell equals N/A then output nothing, otherwise output the cell plus 184.

=IF([ASP Date]@row = "N/A", "", [ASP Date]@row + 184)

Paul Newcome

Try this:

=IF([Time Left]@row = "Past Due", "Red", IF([Time Left]@row = "Complete", "Gray", IF([Time Left]@row <= 3, "Green", IF([Time Left]@row <= 6, "Yellow"))))

Marcela Hernandez

Awesome @Paul Newcome! Thanks so much!

As a follow up, is there a way to disregard a parameter when dealing with dates? The last remaining issue with this health check is that another formula is triggering an error. Since the original Excel formula is not available in Smartsheet, we had to get creative by adding 184 days. When the ASP field has an "N/A" as the value the formula adds the N/A + 184 which returns N/A184 and triggers an #Invalid Operations error.

Cell formula =[ASP Date]@row + 184

Paul Newcome

@Marcela Hernandez You would use another IF statement to say "if the cell equals N/A then output nothing, otherwise output the cell plus 184.

=IF([ASP Date]@row = "N/A", "", [ASP Date]@row + 184)

Marcela Hernandez

Much appreciated @Paul Newcome!! Saved my day! Have a great one. ☺️😊

Paul Newcome

Happy to help. 👍️