Parent Row Symbols based on highest priority?

Laura

It is possible to create a formula that will change the symbol to represent the HIGHEST level out of the 4 rows?

IF the (HIGHEST) Risk Level for rows 1-4 is "x" THEN action for symbol column

is "low" = blue symbol

is "medium" = green symbol

is "high" = yellow symbol

is "extreme" = red symbol

I currently have a formula to show a RYGB symbol that represents that rows Risk Level, but i want the "Parent" row to show the highest level symbol of the 4 rows. 

Example:

Row 2 = Risk Level - LOW

Row 3 = Risk Level - MEDIUM

Row 4 = Risk Level - HIGH

Row 5 = Risk Level - EXTREME

I want Row 1 symbol to show Red Ball Symbol to represent extreme.

If all 4 rows were "Low" then Row 1 symbol will show blue to represent low 

 

Is this possible? 

Laura

=IF(OR(RATING2:RATING:5 = 1, RATING2:RATING5 = 2), "Blue", IF(AND(RATING2:RATING5 >= 3, RATING2:RATING5 <= 6), "Green", IF(AND(RATING2:RATING5 >= 7, RATING2:RATING5 <= 10), "Yellow", IF(OR(RATING2:RATING5 = 11, RATING2:RATING5 = 12), "Red"))))

 

This is what I've tried but isnt working. The # ranges define low, med, high & extreme. I thought going that route might be easier... 

Brent Nathan

Personally instead of low, med, high etc.  i used a number system.

In the parent row of risk level, i used this formula

=MAX(CHILDREN([Risk Level]1))

In the first row of the Risk Level Symbol column i used this formula, then dragged it down across the children.

=IF([Risk Level]1 = "1", "Blue", IF([Risk Level]1 = "2", "Green", IF([Risk Level]1 = "3", "Yellow", IF([Risk Level]1 = "4", "Red"))))

this should achieve what you are trying to accomplish.

Laura

Thanks Brent! I cant ever remember to use MAX formulas!

Brent Nathan

Lol no worries. I know its not your ideal solution but glad I could help

You could also do the reverse, and set the colour balls, which then updates the risk level number.