RYG-ball with nested IF statement (combined with AND)
Hello community,
I simply want to change the status of a RYG-ball 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 semi-colons 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 semi-colons 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 re-typing the formula with commas instead of semi-colons I noticed, that Smartsheet changed the commas back to semi-colons 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 semi-colons 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 semi-colons are the same thing in smartsheet, if you are in europe smartsheet expects you to use semi-colons, 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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 440 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!