Status stoplight ball formula

schletpe

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!

schletpe

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

Michael Culley

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

schletpe

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.

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

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.

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

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