3

Hi,

 

I'm new to smartsheets and trying to become more efficient at writing formulas. I've written four if statements that perform certain tasks based on the criteria. I'd like it to all be one statement that I can put into one of my cells, however I can't quite seem to figure it out. I've read other users posts and some of the Smartsheets documents but I'm still unable to get it working. Here are my four statements:

 

### If the end date is less that today's date, and the column 'Completed' is full,

### then change the RYG Ball to 'Green'. Otherwise, turn it 'red'.

=IF(AND([End Date]3 < TODAY(), [Completed]3 = "Full"), "Green", "Red")



### if End Date is less than today and Completed is not full then make it red

=IF(AND([End Date]3 < TODAY(), [Completed]3 = "Empty"), "Red", "Green")



### if End Date is greater than today make it green

=IF(AND([End Date]3 > TODAY()), "Green", "Red")



### if End Date is 2 days before today and Completed is not full then make it yellow

=IF(AND([End Date]3 < TODAY() - 2, [Completed]3 = "Empty"), "Yellow", "Green")

 

Any help would be greatly appreciated!

Comments

An IF statement is built in the order of =IF(Logical Statement is true, then enter this, otherwise enter this)

 

To nest them, you would put the next IF statement in the otherwise part of the first and close them both out at the same time at the end of the second one.

 

=IF(Statement 1, "Answer One", IF(Statement 1, Answer Two"))

Thanks for the reply, I've continued to test multiple statements to get it working. So far, I keep getting "Unparseable" when I use this statement:


=IF(AND([End Date]3 < TODAY(), [Completed]3 = "Full", "Green", IF(AND([End Date]3 < TODAY(), [Completed]3 = "Empty", "Red"), IF(AND([End Date]3 > TODAY(), "Green", IF(AND([End Date]3 < TODAY() - 2, [Completed]3 = "Empty", "Yellow", "Green") "Red") "Green") "Red"))))

Am I nesting the "IF" statements correctly?

Close. In your first two, you have the correct layout, but you forgot to close your AND statements.

 

Your third IF, you have an AND, but did not include an additional criteria. You will also need to close that AND.

 

Your fourth IF, you didn't close the AND statement.

 

Finally, the multiple "otherwise" (the third part of an IF statement) portions is incorrect. Your first "otherwise is the second IF. Your second otherwise is your third IF. So on and so forth.

 

Here is a formula that does EXACTLY as you have in your first post.

 

=IF(OR(Completed@row = "Full", [End Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(-2), "Yellow", "Red"))

 

Although I feel like what you are trying to achieve is more along the lines of 

 

=IF(OR(Completed@row = "Full", [End Date]@row > TODAY(2)), "Green", IF([End Date]@row > TODAY(), "Yellow", "Red"))

 

Give them both a try with different dates and let me know if either of them get you the results you're looking for.