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
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!