Help with IF AND formula

lindsey.jones291441 · 06/10/19

I appreciate the wealth of knowledge in this community. I have reviewed several scenarios around my similar formula, but still nothing is working. I continue to get #UNPARSEABLE. Hoping some expert out there can help!!

The following formulas work independently, but I need them to work together and drive the RGYG buttons for health.

=IF([Percent of Completion]@row = 1, "Green")

=IF(AND([Percent of Completion]@row < 1, [Due Date]@row < TODAY()), "Red")

=IF(AND([Percent of Completion]@row < 1, [Due Date]@row < TODAY(30)), "Yellow")

=IF([Percent of Completion]@row = 0, "Gray")

Combined I have tried several ways, but this was the best I could come up with:

=IF(([Percent of Completion]@row = 0, "Gray"), IF([Percent of Completion]@row = 1, "Green"), IF(AND([Percent of Completion]@row < 1, [Due Date]@row < TODAY()), "Red"), IF(AND([Percent of Completion]@row < 1, [Due Date]@row < TODAY(30)), "Yellow"))

Thanks in advance, Lindsey Jones

Andrée Starå · 06/10/19

Hi Lindsey,

Try something like this.

The error was that you were closing each IF section.

=IF([Percent of Completion]@row = 0; "Gray"; IF([Percent of Completion]@row = 1; "Green"; IF(AND([Percent of Completion]@row < 1; [Due Date]@row < TODAY()); "Red"; IF(AND([Percent of Completion]@row < 1; [Due Date]@row < TODAY(30)); "Yellow"))))

The same version but with the below changes for your and others convenience.

=IF([Percent of Completion]@row = 0, "Gray", IF([Percent of Completion]@row = 1, "Green", IF(AND([Percent of Completion]@row < 1, [Due Date]@row < TODAY()), "Red", IF(AND([Percent of Completion]@row < 1, [Due Date]@row < TODAY(30)), "Yellow"))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

lindsey.jones291441 · 06/10/19

Yes, it looks like it did! I am going to play around with it and test some more, but so far so good.

THANK YOU!!!!!

Andrée Starå · 06/10/19

Excellent!

Happy to help!

Best,

Andrée

lindsey.jones291441 · 06/11/19

Hi Andree,

Would you know how to take this a step further? Now, I would like to indicate a general status. Looking for a single formula to return either Green, Red or Yellow based on the following:

Green if all elements (rows) are green

Yellow if one or more elements are yellow

Red if one or more elements are red.

Does that make sense?

Andrée Starå · 06/16/19

I've answered in another post: https://community.smartsheet.com/discussion/rgy-status-overall-roll

Did it work?

Best,

Andrée

Help with IF AND formula

Comments

Help Article Resources

Categories