Parent status (RYGB ball) changing based on child status (dropdown)
I am looking for help with a formula where I have a parent row and then multiple children rows below. The Parent has an overall status that is a 4 ball symbol of red, yellow, green, blue)
My child rows are using a dropdown menu called "Confidence" with percentages as the values (10%, 20%...100%).
I'd like to set the the ball color for the parent row to be based on the highest percentage of any of the children.
If the highest child is row is set to 90 or 100% the parent should be blue.
If the highest child row is set to 70 or 80% the parent should be green.
If the highest child row is set to 50 or 60% the parent should be yellow.
If the highest child row is set from 10 to 40% the parent should be red.
Thanks in advance for any assistance!