Stoplight formula based on % Complete
Hi all...
I'm using Smartsheet to create a project plan.
For each task, I have a stoplight column (red, yellow, green). I also have a "% Complete" column, and Baseline Start and Baseline Finish columns. I would like to set a formula to display a stoplight based on percent complete and # of days to Baseline Finish.
So, for example:
0%25% Complete with 7 days or less to Baseline Finish, =Red.
25%75% Complete with 7 days or less to Baseline Finish, =Yellow.
75%100% Complete with 7 days or less to Baseline Finish, =Green
Any thoughts on how to start?
Best Answer

@schletpe I'd first setup a "helper" column to flag when days to Baseline Finish is 7 or less, since you're using the same value across your formula. So make a Checkbox column, let's call it "7 Day Flag" (you can call it whatever you wish), then enter the following formula
=If([Baseline Finish]@row  Today() <=7,True,False)
After entering, right click the cell with the formula and select Make Column Formula
For your Stoplight column, you can then use a formula like this:
=If(AND([7 Day Flag]@row=true,[% Complete]@row<=.25),"Red",If(AND([7 Day Flag]@row=true,[% Complete]@row>=.75),"Green",If(AND([7 Day Flag]@row=true,[% Complete]@row>.25,[% Complete]@row<.75),"Yellow")))
Answers

@schletpe I'd first setup a "helper" column to flag when days to Baseline Finish is 7 or less, since you're using the same value across your formula. So make a Checkbox column, let's call it "7 Day Flag" (you can call it whatever you wish), then enter the following formula
=If([Baseline Finish]@row  Today() <=7,True,False)
After entering, right click the cell with the formula and select Make Column Formula
For your Stoplight column, you can then use a formula like this:
=If(AND([7 Day Flag]@row=true,[% Complete]@row<=.25),"Red",If(AND([7 Day Flag]@row=true,[% Complete]@row>=.75),"Green",If(AND([7 Day Flag]@row=true,[% Complete]@row>.25,[% Complete]@row<.75),"Yellow")))

This is helpful. And it gets me almost where I want it. I'm going to play with this a little and see if I can tweak it even more. For instance...if we're more than 7 days out and the flag is not checked, I may want the stoplight to default to yellow or green based on progress.
Could I add a similar secondary formula around if the 7 Day Flag being false based on % Complete?
So if we're more than 7 days out and the % Complete is 075, the stoplight is Yellow. If it's 75100, it's Green.

Something like this...where am I getting it wrong?
=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"))))

@schletpe I think you're just missing a ). You have 5 IF statements but only 4 ))))

I added an extra parentheses, but it's still unparseable. Hmmm...any thoughts?
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.5K Get Help
 367 Global Discussions
 202 Industry Talk
 432 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!