Using multiple IF(AND) statements in a formula

AdamsR

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

Paul Newcome

Give this a try:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "In Progress", IF([End Date]@row< TODAY(), "Red", IF([End Date]@row< TODAY(30), "Yellow", "Green")))

Paul Newcome

Give this a try:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "In Progress", IF([End Date]@row< TODAY(), "Red", IF([End Date]@row< TODAY(30), "Yellow", "Green")))

AdamsR

Paul, Thank you SO much! That worked perfectly, I was definitely over complicating my formula. I appreciate your expedient response!

Paul Newcome

Happy to help. 👍️