Forum Discussion

Shawna760's avatar
Shawna760
Copper Contributor
Nov 10, 2021
Solved

Conditional Formatting Logic Puzzle

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 logi...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Nov 10, 2021

    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 🙂

mtarler's avatar
mtarler
Silver Contributor
Nov 10, 2021

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 🙂

Resources