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

Options

Hi,

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.

Thanks!

Tags:

• ✭✭✭✭✭✭
Options

This worked for me.

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

• ✭✭✭✭✭✭
Options

Without revealing sensitive information on your sheet, can you provide an example of a few rows with these columns so we can see how your sheet is set up? I'm not sure if you're needing a range of cells or just info in a single row for your conditions.

• Options

Hi Matt! Thanks for the quick reply.

Here's a screenshot. In essence, it's a series of tasks, some of which are past due. Among other fields, I have a field in the Sheet Summary tallying the number of total tasks (I'll worry about children vs parent later), and a field in the Sheet Summary calculating how many tasks are past their end date that are not complete.

• ✭✭✭✭✭✭
Options

This worked for me.

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

• Options

Amazing! Thank you so much! This worked!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!