Display RYG based on calc of two sheet summary fields and IF/OR statements


I'd like to display Red, Yellow, Green in a Sheet Summary field based on the calculation of two other fields, Past Due and Total Tasks. I'd like it to show Red if Past Due divided by Total Tasks is greater than 60%, Yellow if between 40% and 60%, and Green if less than 40%

I'm new to Smartsheet so could be way off:

=IF(OR([Past Due]# / [Total Tasks]# <= 0.4, "Green"), IF(AND([Past Due]# / [Total Tasks]# < 0.41, [Past Due]# / [Total Tasks]# < 0.5), "Yellow"), IF([Past Due]# / [Total Tasks]# > 0.6, "Red")))

Any help would be appreciated.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!