RAID Risk Matrix

WhitneyOliver

Hi,

I have the below matrix, is there any easy way to get the formula to do the various risk levels (low, medium, high and extreme)? I have attempted to try with AI the formula but for some reason starting with the last scenario of Extreme it stopped generating a formula.

Paul Newcome

Give this a try. Fingers moved a little out of order.

=IF([Risk Severity]@row = "4 Intolerable", IF(OR([Risk Likelihood]@row = "4 Almost Certain", [Risk Likelihood]@row = "3 Certain"), "Extreme", "High"), IF([Risk Severity]@row = "3 Undesirable", IF([Risk Likelihood]@row = "4 Almost Certain", "Extreme", IF([Risk Likelihood]@row = "1 Unlikely", "Medium", "High")), IF([Risk Severity]@row = "2 Tolerable", IF([Risk Likelihood]@row = "4 Almost Certain", "High", IF([Risk Likelihood]@row = "1 Unlikely", "Low", "Medium")), IF(OR([Risk Likelihood]@row = "4 Almost Certain", [Risk Likelihood]@row = "3 Likely"), "Medium", "Low"))))

Michelle Choate 2

This is a bunch of IF statements in the [Risk Level] Column

= IF(AND(OR([Risk Likelihood]@row = "1 Unlikely", [Risk Likelihood]@row = "2 Possible"),[Risk Severity]@row = "1 Acceptable"), "Low",

IF(AND([Risk Likelihood]@row = "1 Unlikely", [Risk Severity]@row = "2 Tolerable"), "Low",

IF(AND(OR([Risk Likelihood]@row = "3 Likely", [Risk Likelihood]@row = "4 Almost Certain"),[Risk Severity]@row = "1 Acceptable"), "Medium",

IF(AND(OR([Risk Likelihood]@row = "2 Possible", [Risk Likelihood]@row = "3 Likely"),[Risk Severity]@row = "2 Tolerable"), "Medium",

IF(AND([Risk Likelihood]@row = "4 Almost Certain"),[Risk Severity]@row = "2 Tolerable"), "High",

IF(AND([Risk Likelihood]@row = "4 Almost Certain"),[Risk Severity]@row = "3 Undesirable"), "Extreme"

Remove the paragraph breaks between all of these and make it one long formula and you should be good to go!

WhitneyOliver

Thank you! Unfortunately, for some reason it works up through "Medium", but when adding the last 2 (high and Extreme) it becomes "#incorrect argument set"

Paul Newcome

Do you have this matrix somewhere in Smartsheet whether it be in a separate sheet or the same sheet?

JBG

@WhitneyOliver

I have solved this as follows:

1. Add a column("Likelihood#") in which you score each likelihood, 1=Unlikely —> 4=Almost Certain
2. On row 2, under severity you score each severity as Acceptable=1, Tollerable=2, Undesirable=6 and Intolerrable=8. The last two columns are not just previous+1, because they weigh more in the calculation.
3. With the following formula you could then determine the risk level.
   1. =IF($[Likelihood#]3 * [Column3]$2 < 3; "Low"; IF(AND($[Likelihood#]3 * [Column3]$2 > 2; $[Likelihood#]3 * [Column3]$2 < 7); "Medium"; IF(AND($[Likelihood#]3 * [Column3]$2 > 7; $[Likelihood#]3 * [Column3]$2 < 19); "High"; "Extreme")))
4. The reason I use the numbering is to make the formula much shorter. You could opt to do the checking on the severity values.

Below pictures shows results:

With conditional formatting, you will then do the required coloring.

I hope this helps.

====================================

"Nothing is impossible. The word itself says 'I'm possible!'"

WhitneyOliver

@Paul Newcome - No this is the format I have the RAID Log as in Smartsheet

So far the formula only works up to this point:

=IF(AND(OR([Risk Likelihood]@row = "1 Unlikely", [Risk Likelihood]@row = "2 Possible"), [Risk Severity]@row = "1 Acceptable"), "Low", IF(AND([Risk Likelihood]@row = "1 Unlikely", [Risk Severity]@row = "2 Tolerable"), "Low", IF(AND(OR([Risk Likelihood]@row = "3 Likely", [Risk Likelihood]@row = "4 Almost Certain"), [Risk Severity]@row = "1 Acceptable"), "Medium", IF(AND(OR([Risk Likelihood]@row = "2 Possible", [Risk Likelihood]@row = "3 Likely"), [Risk Severity]@row = "2 Tolerable"), "Medium", ""))))

Adding in the balance of the formula, using the same format, with High and Extreme causes #incorrect argument set

WhitneyOliver

@JPG thanks but that isn't the format I'm looking for. Below is how the RAID Log is set up and I'm trying to get the formula plugged into the "Risk Level" Column. So far I have it up to Medium risk but following the same formula format for High and Extreme causes #incorrect argument set

Paul Newcome

We could index a table if you had it built out in a Smartsheet, but if you prefer to use a nested IF instead of indexing a table, it would look something along the lines of:

=IF([Risk Severity]@row = "4 Intolerable", IF(OR([Risk Likelihood]@row = "4 Almost Certain", [Risk Likelihood]@row = "3 Certain"), "Extreme", "High"), IF([Risk Severity]@row = "3 Undesirable", IF([Risk Likelihood]@row = "4 Almost Certain", "Extreme", IF([Risk Likelihood]@row = "1 Unlikely", "Medium", "High")), IF([Risk Severity]@row = "2 Tolerable", IF([Risk Likelihood]@row = "4 Almost Certain", "High", IF([Risk Likelihood]@row = "1 Unlikely", "Low", "Medium")), IF(OR([Risk Likelihood]@row = "4 Almost Certain", [Risk Likelihood]@orw = "3 Likely"), "Medium", "Low"))))

WhitneyOliver

@Paul Newcome Unfortunately plugging that formula in led to #unparseable

JBG

@WhitneyOliver,

Apologies I misunderstood the question. I thought you wanted to present in the way you showed the matrix.

I looked at the formula of @Paul Newcome. I noticed the last part of [Risk Likelihood]@row is stating ORW iso ROW, therefore the unparseable

Paul Newcome

Give this a try. Fingers moved a little out of order.

=IF([Risk Severity]@row = "4 Intolerable", IF(OR([Risk Likelihood]@row = "4 Almost Certain", [Risk Likelihood]@row = "3 Certain"), "Extreme", "High"), IF([Risk Severity]@row = "3 Undesirable", IF([Risk Likelihood]@row = "4 Almost Certain", "Extreme", IF([Risk Likelihood]@row = "1 Unlikely", "Medium", "High")), IF([Risk Severity]@row = "2 Tolerable", IF([Risk Likelihood]@row = "4 Almost Certain", "High", IF([Risk Likelihood]@row = "1 Unlikely", "Low", "Medium")), IF(OR([Risk Likelihood]@row = "4 Almost Certain", [Risk Likelihood]@row = "3 Likely"), "Medium", "Low"))))

WhitneyOliver

@JBG @Paul Newcome Thank you so much it worked!!!!

Paul Newcome

Happy to help. 👍️