Nested if for RYG Balls and qty not working

Marina Dixon

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

Paul Newcome

Try something like this...

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

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