Display RYG based on calc of two sheet summary fields and IF/OR statements
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!
Best Answer
-
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", "")))
Answers
-
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.
-
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.
-
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", "")))
-
Amazing! Thank you so much! This worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!