Probability and Impact Matrix (5 x 5)

ksprague

Hello,

I have three columns. They are: Probability, Impact, and Risk Score.

The values for each column appear below:

Probability

Almost Certain (90% or higher)

High (70% to 90%)

Moderate (40% to 70%)

Low (20% to 40%)

Very Low (less than 20%)

Impact

Very High

High

Medium

Low

Very Low

Risk Score

Conditional formatting rules were created to populate the color of the cells for Risk Score.

If Probability is ‘Almost Certain (90% or higher)’ and Impact is “Very High” then apply this format “DARK RED” to the Risk Score.

If Probability is ‘Almost Certain (90% or higher)’ and Impact is “High” then apply this format “DARK RED” to the Risk Score.

If Probability is ‘Almost Certain (90% or higher)’ and Impact is “Medium” then apply this format “RED” to the Risk Score.

If Probability is ‘Almost Certain (90% or higher)’ and Impact is “Low” then apply this format “ORANGE” to the Risk Score.

If Probability is ‘Almost Certain (90% or higher)’ and Impact is “Very Low” then apply this format “YELLOW” to the Risk Score.

If Probability is ‘High (70% to 90%)’ and Impact is “Very High” then apply this format “DARK RED” to the Risk Score.

If Probability is ‘High (70% to 90%)’ and Impact is “High” then apply this format “RED” to the Risk Score.

If Probability is ‘High (70% to 90%)’ and Impact is “Medium” then apply this format “ORANGE” to the Risk Score.

If Probability is ‘High (70% to 90%)’ and Impact is “Low” then apply this format “YELLOW” to the Risk Score.

If Probability is ‘High (70% to 90%)’ and Impact is “Very Low” then apply this format “YELLOW” to the Risk Score.

If Probability is ‘Moderate (40% to 70%)’ and Impact is “Very High” then apply this format “DARK RED” to the Risk Score.

If Probability is ‘Moderate (40% to 70%)’ and Impact is “High” then apply this format “ORANGE” to the Risk Score.

If Probability is ‘Moderate (40% to 70%)’ and Impact is “Medium” then apply this format “YELLOW” to the Risk Score.

If Probability is ‘Moderate (40% to 70%)’ and Impact is “Low” then apply this format “YELLOW” to the Risk Score.

If Probability is ‘Moderate (40% to 70%)’ and Impact is “Very Low” then apply this format “GREEN” to the Risk Score.

If Probability is ‘Low (20% to 40%)’ and Impact is “Very High” then apply this format “ORANGE” to the Risk Score.

If Probability is ‘Low (20% to 40%)’ and Impact is “High” then apply this format “YELLOW” to the Risk Score.

If Probability is ‘Low (20% to 40%)’ and Impact is “Medium” then apply this format “YELLOW” to the Risk Score.

If Probability is ‘Low (20% to 40%)’ and Impact is “Low” then apply this format “GREEN” to the Risk Score.

If Probability is ‘Low (20% to 40%)’ and Impact is “Very Low” then apply this format “GREEN” to the Risk Score.

If Probability is ‘Very Low (less than 20%’ and Impact is “Very High” then apply this format “YELLOW” to the Risk Score.

If Probability is ‘Very Low (less than 20%’ and Impact is “High” then apply this format “YELLOW” to the Risk Score.

If Probability is ‘Very Low (less than 20%’ and Impact is “Medium” then apply this format “GREEN” to the Risk Score.

If Probability is ‘Very Low (less than 20%’ and Impact is “Low” then apply this format “GREEN” to the Risk Score.

If Probability is ‘Very Low (less than 20%’ and Impact is “Very Low” then apply this format “GREEN” to the Risk Score.

All of the conditional formatting rules works fine.

GOAL: I want to display the "text" shown below in the Risk Score cell (in addition to the colors that were applied with conditional formatting).

Dark Red = "Severe"

Red = "Major"

Orange = "High"

Yellow = "Medium"

Green = "Low"

PROBLEM STATEMENT: I receive the #UNPARSEABLE error message when attempting to create a nested IF statement based on the following logic.

If Probability = “Almost Certain (90% or higher)” AND impact = “Very High”, “Severe”

If Probability = “Almost Certain (90% or higher)” AND impact = “High”, “Severe”

If Probability = “Almost Certain (90% or higher)” AND impact = “Medium”, “Major”

If Probability = “Almost Certain (90% or higher)” AND impact = “Low”, “High”

If Probability = “Almost Certain (90% or higher)” AND impact = “Very Low”, “Medium”

 

If Probability = “High (70% to 90%)” AND impact = “Very High”, “Severe”

If Probability = “High (70% to 90%)” AND impact = “High”, “Major”

If Probability = “High (70% to 90%)” AND impact = “Medium”, “High”

If Probability = “High (70% to 90%)” AND impact = “Low”, “Medium”

If Probability = “High (70% to 90%)” AND impact = “Very Low”, “Medium”

 

If Probability = “Moderate (40% to 70%)” AND impact = “Very High”, “Major”

If Probability = “Moderate (40% to 70%)” AND impact = “High”, “High”

If Probability = “Moderate (40% to 70%)” AND impact = “Medium”, “Medium”

If Probability = “Moderate (40% to 70%)” AND impact = “Low”, “Medium”

If Probability = “Moderate (40% to 70%)” AND impact = “Very Low”, “Low”

 

If Probability = “Low (20% to 40%)” AND impact = “Very High”, “High”

If Probability = “Low (20% to 40%)” AND impact = “High”, “Medium”

If Probability = “Low (20% to 40%)” AND impact = “Medium”, “Medium”

If Probability = “Low (20% to 40%)” AND impact = “Low”, “Low”

If Probability = “Low (20% to 40%)” AND impact = “Very Low”, “Low”

If Probability = “Very Low (less than 20%)” AND impact = “Very High”, “Medium”

If Probability = “Very Low (less than 20%)” AND impact = “High”, “Medium”

If Probability = “Very Low (less than 20%)” AND impact = “Medium”, “Medium”

If Probability = “Very Low (less than 20%)” AND impact = “Low”, “Low”

If Probability = “Very Low (less than 20%)” AND impact = “Very Low”, “Low”

Am I able to display "text" in the Risk Score cell (in addition to the colors that were applied with conditional formatting)?

If so, do you have any suggestions on how to solve this problem / structure the nested IF statement?

Thanks for your assistance.

Paul Newcome

Your syntax for using AND is off.

If Probability = “Almost Certain (90% or higher)” AND impact = “Very High”, “Severe”

should be

IF(AND(Probability@row = "Almost Certain (90% or higher)", Impact@row = "Very High"), "Severe", IF(AND(................

You also have the wrong quotes in there. Notice how yours are slanted but mine are straight up and down? The slanted ones are called "smart quotes" which (ironically enough) are not recognized as valid characters in a Smartsheet formula. You will need to retype them directly in Smartsheet or in a text editor such as Notepad (not Word).

ksprague

Thank you for your timely response!!! @paulnewcome Your guidance resolved the problem. It is appreciated.

Paul Newcome

Your syntax for using AND is off.

If Probability = “Almost Certain (90% or higher)” AND impact = “Very High”, “Severe”

should be

IF(AND(Probability@row = "Almost Certain (90% or higher)", Impact@row = "Very High"), "Severe", IF(AND(................

You also have the wrong quotes in there. Notice how yours are slanted but mine are straight up and down? The slanted ones are called "smart quotes" which (ironically enough) are not recognized as valid characters in a Smartsheet formula. You will need to retype them directly in Smartsheet or in a text editor such as Notepad (not Word).

ksprague

Thank you for your timely response!!! @paulnewcome Your guidance resolved the problem. It is appreciated.

Paul Newcome

Happy to help. 👍️