Formula for Red, Yellow, Green, Gray balls

Jason Wirl

Hi guys,

I think my logic for the below is right, but I've got a syntax problem somewhere. Results for any "Not Started" are "Incorrect Argument Set". Otherwise it works fine.

=IF(Status@row = "N/A", "Gray", IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", IF([Start Date]@row, TODAY(), "Gray", IF([End Date]@row <= TODAY(), "Red")), IF(Status@row = "In Progress", IF([End Date]@row > TODAY(), "Green", "Yellow")))))

Any fixes appreciated.

Jason Wirl

Paul Newcome

Ignore that last comment. You have a comma in place of an argument in the bold portion...

=IF(Status@row = "N/A", "Gray", IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", IF([Start Date]@row, TODAY(), "Gray", IF([End Date]@row <= TODAY(), "Red")), IF(Status@row = "In Progress", IF([End Date]@row > TODAY(), "Green", "Yellow")))))

Andrée Starå

@Jason Wirl

Not sure if my previous one is correct.

I guess that you want it more like this.

If not, could you write it part by part instead?

=

IF(Status@row = "N/A", "Gray",

IF(Status@row = "Complete", "Green",

IF(Status@row = "Not Started", "ANSWER",

IF([Start Date]@row = TODAY(), "Gray",

IF([End Date]@row <= TODAY(), "Red",

IF(Status@row = "In Progress", "ANSWER",

IF([End Date]@row > TODAY(), "Green", "Yellow")))))))

Jason Wirl

Gents,

Thank you very much! @Andrée Starå, from what I understand, I need to nest some of them together to get the right logic as there are a couple conditions that need to be met for the "answer." Here it is in English to see if I'm doing that right.

If STATUS is "N/A" - gray ball

If STATUS is "Complete" - green ball

If STATUS is "Not Started" - AND START DATE >= TODAY - gray ball, OR END DATE is <= TODAY - red ball

If STATUS is "In Progress" - AND END DATE > TODAY - green ball

Otherwise yellow ball.

As of right now, I changed two things, and it works in all but three rows (not sure why yet) so I'm either on the target or really close. @Paul Newcome's find on the comma where there needed to be an argument was spot on (and I changed that in bold as you see above). I'd just like to understand the syntax for nesting the IF statements according to my logic better. Is there a document/post/anything else you can point me to that could help?

Thanks,

Jason

Paul Newcome

When you nest an IF in the "output if true" portion of another IF, it is basically replacing an AND statement.

=IF(AND(Status@row = "In Progress", Start@row > TODAY()), "Green", IF(AND(Status@row = "In Progress", End@row < TODAY()), "Red"))

In the above you have two IF/AND statements. Both of them share the criteria of Status@row = "In Progress". We can consolidate this by writing it out as

=IF(Status@row = "In Progress", IF(Start@row> TODAY(), "Green", IF(End@row< TODAY(), "Red")))

In this very basic example it doesn't save a whole lot, but when you start getting into longer, more complex nested IFs it can save a lot of typing. It is also helpful when you have additional Status criteria to add more IFs for because you can organize your formula based on the Status.

Looking at your outline from your last post though, I would recommend grouping by ouput (color) instead.

If STATUS is "N/A" - gray ball

If STATUS is "Complete" - green ball

If STATUS is "Not Started" - AND START DATE >= TODAY - gray ball, OR END DATE is <= TODAY - red ball

If STATUS is "In Progress" - AND END DATE > TODAY - green ball

Otherwise yellow ball.

Gray:

Status@row = "N/A"

or

AND(Status@row = "Not Started", [Start Date]@row >= TODAY())

IF(OR(Status@row = "N/A", AND(Status@row = "Not Started", [Start Date]@row >= TODAY())), "Gray"

Green:

Status@row = "Complete"

or

AND(Status@row = "In Progress", [End Date]@row > TODAY())

IF(OR(Status@row = "Complete", AND(Status@row = "In Progress", [End Date]@row > TODAY())), "Green"

Red:

AND(Status@row = "Not Started", [End Date]@row <= TODAY())

IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red"

Yellow:

Everything else

=IF(OR(Status@row = "N/A", AND(Status@row = "Not Started", [Start Date]@row >= TODAY())), "Gray", IF(OR(Status@row = "Complete", AND(Status@row = "In Progress", [End Date]@row > TODAY())), "Green", IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red", "Yellow")))

Andrée Starå

@Jason Wirl

Excellent!

You're more than welcome!

Yes, grouping is a personal preference. I usually break the parts down into different rows in the sheet and test them individually and then put the parts together. It can be tricky to get them right.

If I write the formula in a text editor, I usually format it something like this, so it's easy to see each part.

=
IF(Status@row = "N/A", "Gray",
IF(Status@row = "Complete", "Green",
IF(Status@row = "Not Started", "ANSWER",
IF([Start Date]@row = TODAY(), "Gray",
IF([End Date]@row <= TODAY(), "Red",
IF(Status@row = "In Progress", "ANSWER",
IF([End Date]@row > TODAY(), "Green", "Yellow")))))))

Paul Newcome

I frequently do the same thing as @Andrée Starå where I break it down into sections on different rows for testing and then put it all together once everything is working.

Paul Newcome

Double check that both date columns are in fact set to date type columns. If they are, double check that the data within those cells are definitely being stored as dates and not a text string that just looks like a date.

Andrée Starå

Hi @Jason Wirl

I hope you're well and safe!

There were a couple of issues. (You were missing the true parts, and there were one too many parentheses)

Try something like this.

=

IF(Status@row = "N/A", "Gray", 

IF(Status@row = "Complete", "Green", 

IF(Status@row = "Not Started", "ANSWER", 

IF([Start Date]@row, TODAY(), "Gray", 

IF([End Date]@row <= TODAY(), "Red", 

IF(Status@row = "In Progress", "ANSWER", 

IF([End Date]@row > TODAY(), "Green", "Yellow")))))))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Ignore that last comment. You have a comma in place of an argument in the bold portion...

=IF(Status@row = "N/A", "Gray", IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", IF([Start Date]@row, TODAY(), "Gray", IF([End Date]@row <= TODAY(), "Red")), IF(Status@row = "In Progress", IF([End Date]@row > TODAY(), "Green", "Yellow")))))

Andrée Starå

@Jason Wirl

Not sure if my previous one is correct.

I guess that you want it more like this.

If not, could you write it part by part instead?

=

IF(Status@row = "N/A", "Gray",

IF(Status@row = "Complete", "Green",

IF(Status@row = "Not Started", "ANSWER",

IF([Start Date]@row = TODAY(), "Gray",

IF([End Date]@row <= TODAY(), "Red",

IF(Status@row = "In Progress", "ANSWER",

IF([End Date]@row > TODAY(), "Green", "Yellow")))))))

Jason Wirl

Gents,

Thank you very much! @Andrée Starå, from what I understand, I need to nest some of them together to get the right logic as there are a couple conditions that need to be met for the "answer." Here it is in English to see if I'm doing that right.

If STATUS is "N/A" - gray ball

If STATUS is "Complete" - green ball

If STATUS is "Not Started" - AND START DATE >= TODAY - gray ball, OR END DATE is <= TODAY - red ball

If STATUS is "In Progress" - AND END DATE > TODAY - green ball

Otherwise yellow ball.

As of right now, I changed two things, and it works in all but three rows (not sure why yet) so I'm either on the target or really close. @Paul Newcome's find on the comma where there needed to be an argument was spot on (and I changed that in bold as you see above). I'd just like to understand the syntax for nesting the IF statements according to my logic better. Is there a document/post/anything else you can point me to that could help?

Thanks,

Jason

Paul Newcome

When you nest an IF in the "output if true" portion of another IF, it is basically replacing an AND statement.

=IF(AND(Status@row = "In Progress", Start@row > TODAY()), "Green", IF(AND(Status@row = "In Progress", End@row < TODAY()), "Red"))

In the above you have two IF/AND statements. Both of them share the criteria of Status@row = "In Progress". We can consolidate this by writing it out as

=IF(Status@row = "In Progress", IF(Start@row> TODAY(), "Green", IF(End@row< TODAY(), "Red")))

In this very basic example it doesn't save a whole lot, but when you start getting into longer, more complex nested IFs it can save a lot of typing. It is also helpful when you have additional Status criteria to add more IFs for because you can organize your formula based on the Status.

Looking at your outline from your last post though, I would recommend grouping by ouput (color) instead.

If STATUS is "N/A" - gray ball

If STATUS is "Complete" - green ball

If STATUS is "Not Started" - AND START DATE >= TODAY - gray ball, OR END DATE is <= TODAY - red ball

If STATUS is "In Progress" - AND END DATE > TODAY - green ball

Otherwise yellow ball.

Gray:

Status@row = "N/A"

or

AND(Status@row = "Not Started", [Start Date]@row >= TODAY())

IF(OR(Status@row = "N/A", AND(Status@row = "Not Started", [Start Date]@row >= TODAY())), "Gray"

Green:

Status@row = "Complete"

or

AND(Status@row = "In Progress", [End Date]@row > TODAY())

IF(OR(Status@row = "Complete", AND(Status@row = "In Progress", [End Date]@row > TODAY())), "Green"

Red:

AND(Status@row = "Not Started", [End Date]@row <= TODAY())

IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red"

Yellow:

Everything else

=IF(OR(Status@row = "N/A", AND(Status@row = "Not Started", [Start Date]@row >= TODAY())), "Gray", IF(OR(Status@row = "Complete", AND(Status@row = "In Progress", [End Date]@row > TODAY())), "Green", IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red", "Yellow")))

Jason Wirl

Many thanks @Paul Newcome & @Andrée Starå. Very helpful as always. The grouping by color for easier interpretation and organization makes total sense.

Cheers,

Jason

Paul Newcome

Happy to help. 👍️

really the grouping boils down to personal preference and can be different case by case. Sometimes I group by output. Other times I group by criteria. And then other times I just go brute force because my brain doesn't want to function enough to consolidate. Haha

Andrée Starå

@Jason Wirl

Excellent!

You're more than welcome!

Yes, grouping is a personal preference. I usually break the parts down into different rows in the sheet and test them individually and then put the parts together. It can be tricky to get them right.

If I write the formula in a text editor, I usually format it something like this, so it's easy to see each part.

=
IF(Status@row = "N/A", "Gray",
IF(Status@row = "Complete", "Green",
IF(Status@row = "Not Started", "ANSWER",
IF([Start Date]@row = TODAY(), "Gray",
IF([End Date]@row <= TODAY(), "Red",
IF(Status@row = "In Progress", "ANSWER",
IF([End Date]@row > TODAY(), "Green", "Yellow")))))))

Paul Newcome

I frequently do the same thing as @Andrée Starå where I break it down into sections on different rows for testing and then put it all together once everything is working.

wodifysoftware

@Paul Newcome I am trying to write a formula that will return red, yellow, green, or gray staus symbols based on the following criteria:

• Gray  - If actual % complete = expected % complete
• Green  - if actual % complete more than 10% > expected % complete
• Yellow - if the actual % complete is within 10% of the expected % complete
• Red - if the actual % complete is more than 10% below the expected % complete

Where expected % complete is calculated as follows: =(TODAY() - Start@row) / (Finish@row - Start@row)

Would you be able to help me with the formula? Also please feel free to correct my assumptions if they overlap or are not inclusive.