Nested IF/AND formula
I am new to smartsheets and I am having a hard time with a formula, hopefully someone can help me.
I am trying to create a formula that will indicate the status of a task based on the following condition:
- whether the task is completed or action is required
- whether the task is at risk or not
To conclude this is what I want:
1. At risk + Action Required = Red
2. At risk + No action required = Red
3. risk is blank + action required = Yellow
4. risk is blank + completed = Green
The risk column is either empty or has values that say yes.
The notes column states either completed or action is required.
Below is the formula I have, but I get #unparseable. Any help is very much appreciated.
=IF(AND(Risk2 = "Yes", Notes2 = "Action Required"), "Red", "Yellow")IF(AND(ISBLANK(Risk)2, Note2 = "Completed"), "Green", "Yellow"))
Comments
-
Here is one way to do it:
=IF(ISBLANK(Risk2), IF(Notes2 = "Completed", "Green", "Yellow"), "Red")
Risk is not blank = Red
Risk is Blank/Notes is Completed = Green
Risk is Blank/Notes is anything but Completed = Yellow
-
Thank you so much! I would have never figured this one out. I kept using IF AND nested formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!