Health Check Using Nested IF with Various Criteria

edited 10/03/22

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")

• ✭✭✭✭✭✭

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 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)

• ✭✭✭✭✭✭

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"))))

• 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

• ✭✭✭✭✭✭

@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)

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

• ✭✭✭✭✭✭

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!