Status stoplight ball formula
Hi all...
I have a pretty good working formula to automate task status with stoplight balls based on percentage complete and how close we are to the task's finish date. I have it tied to a 7-day flag column that is flagged when we are within 7 days of finish or past finish date. The stoplight formula is:
=IF(AND([7 Day Flag]@row = true, [% Complete]@row <= 0.25), "Red", IF(AND([7 Day Flag]@row = true, [% Complete]@row >= 0.75), "Green", IF(AND([7 Day Flag]@row = true, [% Complete]@row > 0.25, [% Complete]@row < 0.75), "Yellow", IF(AND([7 Day Flag]@row = false, [% Complete]@row <= 0.75), "Yellow", IF(AND([7 Day Flag]@row = false, [% Complete]@row > 0.75), "Green")))))
As I said, it's working great.
However, I would like to add an additional element. If the task's Status column is "Not Started," I would like the stoplight to default to "Gray." This will allow us to give an accurate picture of which tasks are at risk.
I tried adding the following:
IF([Status]@row = "N/A"), "Gray" and
IF([Status]@row = "blank"), "Gray"
Neither works...
Any ideas? Thanks!
Best Answer
-
That worked! Thank you so much! Here's the final:
=IF(Status@row = "Not Started", "Gray", IF(AND([7 Day Flag]@row = true, [% Complete]@row <= 0.25), "Red", IF(AND([7 Day Flag]@row = true, [% Complete]@row >= 0.75), "Green", IF(AND([7 Day Flag]@row = true, [% Complete]@row > 0.25, [% Complete]@row < 0.75), "Yellow", IF(AND([7 Day Flag]@row = false, [% Complete]@row <= 0.75), "Yellow", IF(AND([7 Day Flag]@row = false, [% Complete]@row > 0.75), "Green"))))))
Answers
-
IF([Status]@row = ""), "Gray"
-
That isn't working.
The Status column is a dropdown with three options: Not Started, Started, Complete.
Does that affect what has to go after the = sign?
-
Hi @schletpe
Yes! The value in the cell will need to match exactly what you have in the formula. For example, you have:
IF([Status]@row = "N/A"), "Gray"
But that would only work if your dropdown option was "N/A". It sounds like your dropdown option is "Not Started" in which case you'll want to have this syntax:
IF([Status]@row = "Not Started"), "Gray"
You could also add in Michael's suggestion above (which is for when the Status cell is blank). Keep in mind you'll want to add it at the very beginning of your formula so it's the first thing that's evaluated.
So in your full formula:
=IF([Status]@row = "Not Started", "Gray", IF(AND([7 Day Flag]@row = true, [% Complete]@row <= 0.25), "Red", IF(AND([7 Day Flag]@row = true, [% Complete]@row >= 0.75), "Green", IF(AND([7 Day Flag]@row = true, [% Complete]@row > 0.25, [% Complete]@row < 0.75), "Yellow", IF(AND([7 Day Flag]@row = false, [% Complete]@row <= 0.75), "Yellow", IF(AND([7 Day Flag]@row = false, [% Complete]@row > 0.75), "Green"))))))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thanks Genevieve. This is helpful. I feel like it's almost there, but still is unparseable.
I'm trying to figure out what's missing. I have it set to trigger gray if the status is "Not Started" and pretty much cut and pasted the formula above.
The stoplight column is set to show red, yellow, green, and gray.
If I delete the added piece for the gray ball, the formula works fine...just for the green, yellow, red balls.
Am I missing a parentheses somewhere? I've counted and I think I have everything closed up.
I'm stumped.
-
Hi @schletpe
My apologies, I noticed in my examples above I have an extra closing parenthses:
IF([Status]@row = "Not Started"), "Gray"
Should be
IF([Status]@row = "Not Started", "Gray"
Moving right from the logic statement to the value if true. Can you post what you have open in your sheet?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
That worked! Thank you so much! Here's the final:
=IF(Status@row = "Not Started", "Gray", IF(AND([7 Day Flag]@row = true, [% Complete]@row <= 0.25), "Red", IF(AND([7 Day Flag]@row = true, [% Complete]@row >= 0.75), "Green", IF(AND([7 Day Flag]@row = true, [% Complete]@row > 0.25, [% Complete]@row < 0.75), "Yellow", IF(AND([7 Day Flag]@row = false, [% Complete]@row <= 0.75), "Yellow", IF(AND([7 Day Flag]@row = false, [% Complete]@row > 0.75), "Green"))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!