If than statement

Teodora

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

IanN

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