If thing in column x is something and thing in column y is something else....errr

Sailor_Trash

I'm trying to build a bit of logic for a risk assessment. I'm brand new to SS, so please excuse the n00b question.

Previously, my risk matrix was 5 x 5 Likelihood x Impact, where 4x3=12 and 3x4=12. 12 was the risk rating.

Now, "Possible 3" x "High 4" = "12 High".......but "Likely 4" x "Moderate 3" = 12 Medium which has shot my very simple formula out the window.

Is there a way to, based on the word value in columns x and y, automatically have resulting value (Medium Risk or High Risk for example) inserted into the third column z?

Paul Newcome

So my suggestion would be to create the chart in a separate sheet.

Then you would use that same formula above but use cross sheet references created to match what is described in the formula.

Paul Newcome

If you change your reference table labels (first column and row 6) to match exactly what the in sheet selections are, you can use something like this...

=INDEX([1 - Low]1:[5 - Extreme]5, MATCH([Likelihood (x)]@row, Likelihood1:Likelihood5, 0), MATCH([Impact (y)]@row, [1 - Low]6:[5 - Extreme]6, 0))

Basically you are using the INDEX function to pull the appropriate value. The first MATCH function gives us a row number based on the likelihood match in the first column of your table, and the second MATCH function tells us which column to pull from based on the match of Impact there across the bottom row.

Sailor_Trash

Thanks very much for the response!

Sorry - I should have been more clear - the coloured table is out of a separate document - so not a reference point. It is the "policy", and I'm building (attempting to anyway) the tool.

How I intend it to work, is my users will select 1 of 5 options from each of the dropdowns in columns "Likelihood" and "Impact" - displayed below (previously referred to as columns X and Y):

I'd then like the column "Inherent Risk Rating" (previously referred to as column Z) to be auto-populated with the calculated product of the 2 columns, based on the risk matrix below:

Essentially the calculation is Likelihood x Impact = Inherent Risk Rating...but my problem is that "Possible x High = High" but "Likely x Moderate = Medium" - so assigning numeric values is no longer of use because the Risk Rating of "3 x 4" does not equal the same Risk Rating of "4 x 3".

Again, thanks very much for your assistance!

Paul Newcome

So my suggestion would be to create the chart in a separate sheet.

Then you would use that same formula above but use cross sheet references created to match what is described in the formula.