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

 

L_123

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 non-integers, and therefore you don't need criteria for the red statement.