SOLVED

Conditional Formatting Logic Puzzle

Copper Contributor

Hey all, 

 

This feels like a big logic puzzle to me but I don't have enough tools in my toolbox to solve it. I need to be able to set up some type of conditional formatting rule or an if/then logical formula to output a letter, a number and a change of color to a cell based on a matrix. I can do one or two of those things but am at a loss for how to do all three of these things at once (letter, number, and color change).

 

See example attached.

 

Thoughts? 

3 Replies

@Shawna760 

In order to have unique column and row names i changed 1, 2 , 3 , 4 and 5 to 1 Severity, 2 Severity and so on and to 1 Probability , 2 Probability and so on. Then i worked with named ranges and with INDIRECT formula to select value from range C3:G7.

 

The named ranged are "  _1_Severity " for example (this is done automatically) and therefore i had to enter the exact names of the named ranges in B13:C18. Finally 5 rules for conditional formatting were entered in order to format a cell in one of the 5 colors of range C3:G7.

 

 

 

best response confirmed by Shawna760 (Copper Contributor)
Solution

@Shawna760 I have included 2 options for the text but for the color (conditional formatting) you will need 5 rules.

For the value in the cell you could use INDEX() as a lookup or 'build' it based on the probability converting to a letter and then appending the severity.

For the color (conditional formatting) I show how by multiplying the 2 values you can create distinct ranges for each zone.

hope this helps and btw I am in quality and regulatory so very familiar with your risk assessment chart :)

Thank you very much @OliverScheurich and @mtarler! I took a look at both of your solutions and understand the conditional formatting. I will spend some time looking into the index, indirect and char formulas to understand them better but I can see that all of them accomplish the goal. @mtarler fun connection, you got it, definitely risk assessment.

All the best,

Shawna
1 best response

Accepted Solutions
best response confirmed by Shawna760 (Copper Contributor)
Solution

@Shawna760 I have included 2 options for the text but for the color (conditional formatting) you will need 5 rules.

For the value in the cell you could use INDEX() as a lookup or 'build' it based on the probability converting to a letter and then appending the severity.

For the color (conditional formatting) I show how by multiplying the 2 values you can create distinct ranges for each zone.

hope this helps and btw I am in quality and regulatory so very familiar with your risk assessment chart :)

View solution in original post