# Calculate a single number for 9 permutations

##### Board Regular
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?

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Gerald Higgins

##### Well-known Member
If parameter 1 is in cell A1, and parameter 2 is in cell A2, how about
Code:
``=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.

##### Board Regular
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

Replies
2
Views
158
Replies
1
Views
129
Replies
2
Views
1K
Replies
1
Views
895
Replies
10
Views
1K

1,191,633
Messages
5,987,783
Members
440,110
Latest member
albertod8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back