Stoplight formula based on % Complete

schletpe

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?

Brian_Richardson

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

Brian_Richardson

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

schletpe

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 0-75, the stoplight is Yellow. If it's 75-100, it's Green.

schletpe

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

Brian_Richardson

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

schletpe

I added an extra parentheses, but it's still unparseable. Hmmm...any thoughts?