Trying to evaluate multiple columns with an IF statement
I keep getting a syntax error for some reason but can't figure out why. I am basically trying to set a status icon of 'Sunny" if the status of the risk is "closed" and if not closed, i am using the other icons based on the level of the risk. here is the formula:
=IF([Status]@row = “Closed”, “sunny”, IF([Risk Score]@row <= 25, "partly sunny", IF([Risk Score]@row > 25, IF([Risk Score]@row <= 50, "cloudy", IF([Risk Score]@row > 50, IF([Risk Score]@row <= 90, "rainy", "stormy"))))))
anyone have any ideas what i am doing wrong?
Best Answer
-
I think you are overcomplicating it. Try this instead:
=IF([Status]@row = "Closed", "sunny", IF([Risk Score]@row <= 25, "partly sunny", IF([Risk Score]@row <= 50, "cloudy", IF([Risk Score]@row <= 90, "rainy", "stormy"))))
It also looks like some of your quotes are slanted and others are straight up and down. The slanted ones are called "Smart Quotes" which (ironically enough) are not recognized as valid characters in Smartsheet formulas. You will need to either retype them here in the Community (which I did above already), directly in Smartsheet, or in a text editor such as Notepad (not Word).
Answers
-
I think you are overcomplicating it. Try this instead:
=IF([Status]@row = "Closed", "sunny", IF([Risk Score]@row <= 25, "partly sunny", IF([Risk Score]@row <= 50, "cloudy", IF([Risk Score]@row <= 90, "rainy", "stormy"))))
It also looks like some of your quotes are slanted and others are straight up and down. The slanted ones are called "Smart Quotes" which (ironically enough) are not recognized as valid characters in Smartsheet formulas. You will need to either retype them here in the Community (which I did above already), directly in Smartsheet, or in a text editor such as Notepad (not Word).
-
Thanks Paul. you are awesome! that did it. appreciate the help!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!