Nested if for RYG Balls and qty not working

I am trying to use the RYG balls when noting when a variance has gone over certain amounts:

5000 or less - Green

5000-10000 - Yellow

over 10000 - Red

I can add grey as my false statement if the coordinator has checked off the completed box. This is the formula I have tried to build so far but I get the Unparseable error. I know I do not have the false statement included, but I'm not sure how to reference a checkbox.

=IF(Variance1<=5000, "Green", IF(AND(Variance1<=10000,(Variance1 "Yellow", IF(Variance1 >10000, "red")))

Find more posts tagged with

Sheets

Formulas

Support

functionality

Accepted answers

Paul Newcome

Try something like this...

=IF(Variance@row <= 5000, "Green", IF(Variance@row <= 10000, "Yellow", "Red"))

All comments

Paul Newcome

Try something like this...

=IF(Variance@row <= 5000, "Green", IF(Variance@row <= 10000, "Yellow", "Red"))

Marina Dixon

Thank you!!! It worked perfectly.

Paul Newcome

Happy to help! 👍️

Marina Dixon

Hi i'm back!

So as I've built out this sheet, I don't like that my blank lines are showing as green balls. To expand my formula into grey balls I added:

=IF(Variance@row <= 5000, "Green", IF(Variance@row <= 10000, "Yellow", IF(Variance@row > 10001, "Red", "Grey")))

Because my <=5000 causes there to never be a false/" ", how do I add an AND statement to account for blanks? Every time I try, I get an Incorrect Argument Set error.

Paul Newcome

We can actually just add an IF to the beginning to say that IF the Variance column equals blank, then output a "Grey".

=IF(Variance@row = "", "Grey", IF(Variance@row <= 5000, "Green", IF(Variance@row <= 10000, "Yellow", IF(Variance@row > 10001, "Red"))))

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.