Calculate a single number for 9 permutations

[datadrvn]

I keep track of risks. Risks have two parmameters; probability of occurrence and business impact if it occurs. Both of these factors are usually rated in a L-M-H manner. I often sort by these factors so I've used numbers to facilitate sorting, e.g., 1=High, 2=Med, and 3=Low and is the same for both parameters.

What I'm trying to do is create a composite measure of each risk so that all 9 permutations can result in a unique number. This way I can prioritize the risks based on their overall "risk rating." My intent is to show the coun't of risks in a 3x3 grid. The problem is my math ability. How can I come up with a calculation that results in 9 unique values based on this? For example, since 1*2 is the same as 2*1, I cannot easily distinquish a low probability risk with medium impact from a medium probability risk with a low impact, which would result in a different placement on the grid.

Since nested IF statements can only handle 7 situations, and I have 9, I can't take that approach. I thought about a sumproduct approach from within each square on the grid, but that gets messy. I figure a single number will make it quite easy to count.

Ideas?

Thanks in advance. Wayne

 

[Gerald Higgins]

If parameter 1 is in cell A1, and parameter 2 is in cell A2, how about

=a1&a2

which will generate numbers such as 12 or 21.

This has an advantage in that the number of permutations is unlimited. For example, if you wanted to switch to ranking the items 1-9 instead of 1-3, then this would work just as well.

The disadvantage of this is that the results would not necessarily be sortable into a meaningful order. 12 and 21 would not appear together - 13 would be between them.

But I think that comes back to a logical problem with what you are trying to do.
On the one hand, you want to combine the two parameters into a single "risk rating". On the other hand, you still want to "distinquish a low probability risk with medium impact from a medium probability risk with a low impact".
I don't think you can do both. Or at least, you can do both, but not in the same calculation.
If you want to maintain the distinction between these types of factors, then having a single risk rating may be futile.

 

[datadrvn]

Thank you, Gerald. I think your solution will work. Since I'm not looking to sort the physical list by this "aggregate" figure, the downside you mention is not a big deal. I can / will still sort the physical list by the respective individual parameters. However, having the combined value, I can test for that quite simply with a countif() in the 3x3 grid I mentioned and everything will line up perfectly (see text version below). Thanks again.

Risk Cube

Impact H 1 0 0
M 0 0 0
L 0 0 0
L M H
Probability