Status stoplight ball formula

Options

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!

Tags:

Best Answer

  • schletpe
    schletpe
    Answer ✓
    Options

    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

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    IF([Status]@row = ""), "Gray"

  • schletpe
    Options

    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?

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 10/25/22
    Options

    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

  • schletpe
    Options

    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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?

  • schletpe
    schletpe
    Answer ✓
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!