Risks Matrix Help

[henrythompson]

Hi all

I’ve a formula I need help with please to generate an output for a risks matrix.

The matrix is for Likelihood v’s Impact, the output being Criticality.

In the application we will select Likelihood & Impact from dropdowns in two adjacent cells, A1 and B1 and we want the outcome in C1.

The matrix of desired input’s v’s outputs is in the attached.

It would be great if the cell background colours in C1 were as in the matrix too.

Last time around we got it to work with the Low, Medium and High categories but just can’t seem to extend it to work with the additional Very High.

Hope someone can help, many thanks in advance.

Henry.


..............................................................
Henry Thompson
Resource Planner

[ChemistB]

Hi Henry

I set up a table of Criticality and named the body of the table "Table", the x axis, "Likelyhood" and the y axis "Impact".

I used this formula to return the proper criticality from that table

Please Login or Register  to view this content.

The Match Function returns the proper row and column which is indexed to the table and returns the proper value.

To get the proper color, I used Conditional Formatting.

Here's the finished product. Let me know if this works for you or if you have any questions.

ChemistB

[henrythompson]

Hi, that's very cool, I didn't even know those functions existed! When we did it last time around we used some nested IF functions. We've now copied your route to the solution and my colleague has it working fine in the application, thanks again and for the quick feedback, Henry.

[ChemistB]

Glad it worked for you.

ChemistB