RYGball with nested IF statement (combined with AND)
Hello community,
I simply want to change the status of a RYGball by comparing two conditions.
If Condition1 AND Condition2 are FALSE than the ball should be RED.
If Condition1 AND Condition2 are TRUE than the ball should be GREEN.
In any other case the ball should be YELLOW.
When building the formula, this worked as an intermediate result
=IF(AND(Condition1 = 0; Condition2 = 0);"Red";"Green")
but adding an additional condition ended up with UNPARSABLE:
=IF(AND(Condition1 = 0; Condition2 = 0); "Red";IF(AND(Condition1 = 1;Condition2 = 1); "Green; "Yellow"))
I know this is not rocket science, but I am stuck. Help articles did not really help me. I guess it's the syntax or brackets.
Can someone have a quick look on my problem?
Thank you in advance.
Best regards,
Holger
Best Answer

After changing the semicolons to commas for my area your formula works for me.
=IF(AND(Condition1 = 0, Condition2 = 0), "Red", IF(AND(Condition1 = 1, Condition2 = 1), "Green", "Yellow"))
Can you post a snip of your sheet with any confidential information hidden so I can see how it is set up?
Answers

You are missing a quote on green in your second formula.

True  but formula still refuses to work and replies with #UNPARSEABLE.
Any further ideas?
Holger

After changing the semicolons to commas for my area your formula works for me.
=IF(AND(Condition1 = 0, Condition2 = 0), "Red", IF(AND(Condition1 = 1, Condition2 = 1), "Green", "Yellow"))
Can you post a snip of your sheet with any confidential information hidden so I can see how it is set up?

At least I am happy, that the Syntax was correct.
After retyping the formula with commas instead of semicolons I noticed, that Smartsheet changed the commas back to semicolons after hitting RETURN. Now the formula was working as expected.
Is it possible that there is any hidden localization topic? I am working with settings set to German. So "Yellow" would be "Gelb". I am not really convinced that commas are translated to semicolons the same way, or does it?
Nevertheless this statement made it (for row 595):
=IF(AND(ColPri595 = 0; ColFo595 = 0); "Rot"; IF(AND(ColPri595 = 1; ColFo595 = 1); "Grün"; "Gelb"))
Thank you for your quick and helpful support.👍️
Holger😀

Commas and semicolons are the same thing in smartsheet, if you are in europe smartsheet expects you to use semicolons, elsewhere commas. It is surprising to me that switching to commas fixed it, I would have expected smartsheet to not even try to read it. There must have been some kind of smartsheet side error. Glad you got it sorted :)
Help Article Resources
Categories
Check out the Formula Handbook template!