Stoplight formula based on % Complete

schletpe
schletpe
edited 09/07/22 in Formulas and Functions

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?

Tags:

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers
    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")))

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers
    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")))

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • 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.

  • 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
    Brian_Richardson Overachievers

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

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!