Need to edit this formula & I can't figure it out

Mary Vogt

Hi,

I am using this formula in a dots column (red, yellow, green).

We are measuring completion for training. There is a 5-hour training and a 25-hour training. Originally, the 5-hour had to be completed by the end of August but apparently that didn't happen. So we had it set up that if 5-hour AND 25-hour is complete, it would be green. If 5-hour 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 25-hour training have NOT completed the 5-hour training, so that needs yet another color. Originally, no one who finished 25-hour would have not first done 5-hour but that hasn't happened. So, they need to know who has completed 25-hour but not 5-hour.

Current formula:

=IF([25-Hour Training Complete]@row = "", IF([5-Hour Training Complete]@row = "", "Red", "Yellow"), "Green")

If I wanted Grey to represent people who have finished 25-hour training but not 5-hour, how would I work that into this formula? Thank you to whoever can help.

Sean Morgan

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([5-Hour Training Complete]@row <> "", [25-Hour Training Complete]@row <> ""), "Green", IF(AND([5-Hour Training Complete]@row = "", [25-Hour Training Complete]@row = ""), "Red", IF(AND([5-Hour Training Complete]@row <> "", [25-Hour Training Complete]@row = ""), "Yellow", IF(AND([5-Hour Training Complete]@row = "", [25-Hour Training Complete]@row <> ""), "Gray"))))

Let me know if you face any issues or have any questions!

Regards

Sean

David Joyeuse

@Mary Vogt

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?

David Joyeuse

@Mary Vogt

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.

Sean Morgan

The pleasure is ours :D

Regards

Sean

Sean Morgan

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([5-Hour Training Complete]@row = 1, [25-Hour Training Complete]@row = 1), "Green", IF(AND([5-Hour Training Complete]@row = 0, [25-Hour Training Complete]@row = 0), "Red", IF(AND([5-Hour Training Complete]@row = 1, [25-Hour Training Complete]@row = 0), "Yellow", IF(AND([5-Hour Training Complete]@row = 0, [25-Hour Training Complete]@row = 1), "Grey"))))

Here are the results, meeting the conditions:

Let me know if you have any questions!

Regards

Sean

David Joyeuse

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([5-Hour Training Complete]@row = 1, [25-Hour Training Complete]@row = 1), IF([5-Hour Training Complete]@row = 1, "Yellow", "Grey"), IF(AND([5-Hour Training Complete]@row = 1, [25-Hour Training Complete]@row = 1), "Green", "Red"))

Hope it helped!

Mary Vogt

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!

Mary Vogt

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.

Sean Morgan

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([5-Hour Training Complete]@row <> "", [25-Hour Training Complete]@row <> ""), "Green", IF(AND([5-Hour Training Complete]@row = "", [25-Hour Training Complete]@row = ""), "Red", IF(AND([5-Hour Training Complete]@row <> "", [25-Hour Training Complete]@row = ""), "Yellow", IF(AND([5-Hour Training Complete]@row = "", [25-Hour Training Complete]@row <> ""), "Gray"))))

Let me know if you face any issues or have any questions!

Regards

Sean

David Joyeuse

@Mary Vogt

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?

Mary Vogt

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

DEMetricsSS.PNG

DEDashboard.PNG

Mary Vogt

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

David Joyeuse

@Mary Vogt

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.

Sean Morgan

The pleasure is ours :D

Regards

Sean