Need to edit this formula & I can't figure it out
Hi,
I am using this formula in a dots column (red, yellow, green).
We are measuring completion for training. There is a 5hour training and a 25hour training. Originally, the 5hour had to be completed by the end of August but apparently that didn't happen. So we had it set up that if 5hour AND 25hour is complete, it would be green. If 5hour only was completed, it would be yellow. And if no training was completed, it would be red.
Now, they have told me that some people who have done 25hour training have NOT completed the 5hour training, so that needs yet another color. Originally, no one who finished 25hour would have not first done 5hour but that hasn't happened. So, they need to know who has completed 25hour but not 5hour.
Current formula:
=IF([25Hour Training Complete]@row = "", IF([5Hour Training Complete]@row = "", "Red", "Yellow"), "Green")
If I wanted Grey to represent people who have finished 25hour training but not 5hour, how would I work that into this formula? Thank you to whoever can help.
Best Answers

Hello @Mary Vogt ,
Thank you for the screenshot, this will help me restructure the Formula. The checkboxes are not required, but were a way to prevent any random value being added into the cells, to alter the color of the Harvey Balls.
With regards to the "dots column", I assume this is the Coloured Harvey Balls? RYG?
If so, here is a modified version of the my Formula. Please note that the Formula could be shortened down as shown by @David Joyeuse , but I right my Formulas as such due to the way I read and write them :)
=IF(AND([5Hour Training Complete]@row <> "", [25Hour Training Complete]@row <> ""), "Green", IF(AND([5Hour Training Complete]@row = "", [25Hour Training Complete]@row = ""), "Red", IF(AND([5Hour Training Complete]@row <> "", [25Hour Training Complete]@row = ""), "Yellow", IF(AND([5Hour Training Complete]@row = "", [25Hour Training Complete]@row <> ""), "Gray"))))
Let me know if you face any issues or have any questions!
Regards
Sean

What kind of INVALID ERROR are you getting with these formulas?
INVALID DATA TYPE?
INVALID VALUE
or INVALID OPERATION?
Is it possible you selected RYGB instead of RYGG for your Progress column?

If you're trying to count "Blue" within a RYGG balls, it will return 0, so you have to replace that with Grey. If the metric is done out of this, then Grey will be displayed by itself. You won't have to add it.
Now I would also suggest you to add a few tests rows with dummy data that would return a Grey ball and see what happens then.

The pleasure is ours :D
Regards
Sean
Answers

Hello @Mary Vogt ,
I have created a solution that may work, but in the instance I have misunderstood the criteria, you could modify my Formula, or let me know and I'll com back and make the required changes! Furthermore, as I'm unsure on your current Sheet layout, I have added a checkbox, but this is not required.
The Formula I used was:
=IF(AND([5Hour Training Complete]@row = 1, [25Hour Training Complete]@row = 1), "Green", IF(AND([5Hour Training Complete]@row = 0, [25Hour Training Complete]@row = 0), "Red", IF(AND([5Hour Training Complete]@row = 1, [25Hour Training Complete]@row = 0), "Yellow", IF(AND([5Hour Training Complete]@row = 0, [25Hour Training Complete]@row = 1), "Grey"))))
Here are the results, meeting the conditions:
Let me know if you have any questions!
Regards
Sean

Hi @Mary Vogt
@Sean Morgan 's formula is working pretty fine, and I support the use of a checkbox in your case, it's easier to read the sheet.
Still, I think we can shorten his formula a small bit to make the sheet faster.
=IF(OR([5Hour Training Complete]@row = 1, [25Hour Training Complete]@row = 1), IF([5Hour Training Complete]@row = 1, "Yellow", "Grey"), IF(AND([5Hour Training Complete]@row = 1, [25Hour Training Complete]@row = 1), "Green", "Red"))
Hope it helped!

Thank you so much both of you for your help! I tried both formulas, without adding the check box columns, and both returned an "INVALID" in the dots column (i replaced the original formula with yours). I'm attaching a screenshot of the layout in case there is something that I didn't accurately describe in my original question. Maybe that would help if you want to edit your formulas? And are the check box columns necessary for the formulas to work?
THank you both so much for working on this with me!

Just want to add, you can see the columns that feed into another 'metrics' sheet, which then feed to a dashboard, in case you are wondering about the link marks.

Hello @Mary Vogt ,
Thank you for the screenshot, this will help me restructure the Formula. The checkboxes are not required, but were a way to prevent any random value being added into the cells, to alter the color of the Harvey Balls.
With regards to the "dots column", I assume this is the Coloured Harvey Balls? RYG?
If so, here is a modified version of the my Formula. Please note that the Formula could be shortened down as shown by @David Joyeuse , but I right my Formulas as such due to the way I read and write them :)
=IF(AND([5Hour Training Complete]@row <> "", [25Hour Training Complete]@row <> ""), "Green", IF(AND([5Hour Training Complete]@row = "", [25Hour Training Complete]@row = ""), "Red", IF(AND([5Hour Training Complete]@row <> "", [25Hour Training Complete]@row = ""), "Yellow", IF(AND([5Hour Training Complete]@row = "", [25Hour Training Complete]@row <> ""), "Gray"))))
Let me know if you face any issues or have any questions!
Regards
Sean

What kind of INVALID ERROR are you getting with these formulas?
INVALID DATA TYPE?
INVALID VALUE
or INVALID OPERATION?
Is it possible you selected RYGB instead of RYGG for your Progress column?

Hey Sean, that last formula seems to work! I can't thank you enough! Now, I am wondering how I need to alter my metrics sheet to reflect the new formula if I do. In response to David's question, the columns' Harvey Balls are RYGG. But I notice on my metrics sheet, it says Blue where it should say grey, but I don't know if that would alter anything. I don't think so. The incoming links in the metrics show:
=COUNTIFS({DET Progress}, Variable@row)
But you can see, where it says Blue I would think it would be counting greys, and there don't appear to be any yet. (See screen shot). I will also attach a screenshot of the Dashboard the metrics feed into. You are both so helpful thank you! In the dashboard I would change the 'legend' to include grey manually, obviously :)

Great news! I recreated the metrics and now gray is showing up. I think we're good to go! Thank you both so much. 😍

If you're trying to count "Blue" within a RYGG balls, it will return 0, so you have to replace that with Grey. If the metric is done out of this, then Grey will be displayed by itself. You won't have to add it.
Now I would also suggest you to add a few tests rows with dummy data that would return a Grey ball and see what happens then.

The pleasure is ours :D
Regards
Sean
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!