If than statement
Hello,
I need your help. I would like to use If statment in conjunction with symbols. The following is the statement that works only , got blank, green and yellow , but I cannot get it to work for red.
=IF(ISBLANK([2019 Budget to Forecast Variance %]50), "", IF([2019 Budget to Forecast Variance %]50 <= 0, "Green", IF(ABS([2019 Budget to Forecast Variance %]50) > 0, "Yellow", IF([2019 Budget to Forecast Variance %]50 >= 10, "Red"))))
Thanks,
Tia
Comments

Hello Teodora,
With your current formula, any positive integer will return a Yellow dot because they're all greater than 0. Once the formula finds an argument it can use, it will ignore the rest of the formula.
In this case, you might instead consider using something like the following:
=IF(ISBLANK([2019 Budget to Forecast Variance %]@row), "", IF([2019 Budget to Forecast Variance %]@row <= 0, "Green", IF(AND(ABS([2019 Budget to Forecast Variance %]@row) >= 0, ABS([2019 Budget to Forecast Variance %]@row) <= 10), "Yellow", IF([2019 Budget to Forecast Variance %]@row >= 10, "Red"))))
To resolve the behavior of Yellow vs. Red, I added a qualifying statement so that integers between 0 and 10 will show yellow. Anything greater than or equal to 10 will now show "Red".
There is likely a more efficient way to achieve this and someone might offer a better solution, but I did test this and know it works.
By the way, the @row function in my formula will help keep processing times down on your formula as your sheet grows: https://help.smartsheet.com/articles/2476491. If you use specific cell references ([2019 Budget to Forecast Variance %]50 for instance), every time you delete a row or add a row, the formula will have to change it's reference. With @row, no matter where the formula is on the sheet, the formula stays the same.
Hope that helps!
Ian

You can simplify the statement a fair bit.
=IF(ISBLANK([2019 Budget to Forecast Variance %]@row), "", IF([2019 Budget to Forecast Variance %]@row <= 0, "Green", IF(ABS([2019 Budget to Forecast Variance %]@row <= 10, "Yellow", "Red"
You don't need the and >=0 statement because you already checked that criteria in the first if statement, and are returning the false value of <=0. All values that make it to this level must be > 0.
On that same note, you check <= 10 on the second if statement, so on the false branch of this statement you are sure both that the number is <=0 and <= 10. The only values possible are numbers greater than 10, and nonintegers, and therefore you don't need criteria for the red statement.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 123 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives