Using multiple IF(AND) statements in a formula

Options

Hello, I am trying to create a column formula that will update Schedule health to a red, yellow, green, or blue circle using multiple IF and IF(AND) statements, but I continuously get the error message "#INCORRECT ARGUMENT SET"

Here is my formula:

=IF(IF(AND([End Date]@row - [Today's Date]@row <= 0, Status@row = "In Progress"), "Red"), IF(AND((15 >= [End Date]@row - [Today's Date]@row <= 30), Status@row = "In Progress"), "Yellow"), IF(AND(([End Date]@row - [Today's Date]@row >= 30), Status@row = "In Progress"), "Green"), IF(Status@row = "Complete", "Blue", " "))


Here is a screen shot of the columns and the formula in smartsheet:


I am not sure what I am doing wrong, and I've been fiddling with this formula for a few days now. Can anyone help me with this issue? Do I need to separate conditions further? for ex:

Instead of: IF(AND((15 >= [End Date]@row - [Today's Date]@row <= 30), Status@row = "In Progress"), "Yellow")

Do : IF(AND([End Date]@row - [Today's Date]@row <= 30, [End Date]@row - [Today's Date]@row <=15, Status@row = "In Progress"), "Yellow")


Any help would be greatly appreciated

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!