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

Janine Santangelo

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!

Mike TV

@Janine Santangelo

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", "")))

Mike TV

@Janine Santangelo

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.

Janine Santangelo

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.

Mike TV

@Janine Santangelo

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", "")))

Janine Santangelo

Amazing! Thank you so much! This worked!