RYG-ball with nested IF statement (combined with AND)

Holger Gemassmer

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

Find more posts tagged with

Formulas

Accepted answers

L_123

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?

All comments

L_123

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

Holger Gemassmer

True - but formula still refuses to work and replies with #UNPARSEABLE.

Any further ideas?

Holger

L_123

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?

Holger Gemassmer

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😀

L_123

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.