Calculating Results Into a Matrix of Text Values

scattered

New Member
Joined
Feb 24, 2002
Messages
3
I am trying to calculate, using multiple nested "If" statements, a risk value ("high", "medium", or "low") that populates a 3x3 matrix. I want to construct arguments such that if a value in column 1 is a certain text value (e.g., text value "likely, "possible", or "unlikely")and a value in a second column is another text value (e.g., "minimal", "moderate", or "significant"), the formula returns the value "high", "medium", or "low" based on the intersction of the text values in the 3x3 matrix.

My attempt at nesting the If statements continously returns errors, and I note that the Excel Help file seem to have a limit of seven nested entries; I seem to need nine arguments? Can you offer a suggested solution? Thanks.
 

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.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
some general suggestions:

1) download the html maker addin from the link below this post. use it to take a snapshot of your spreadsheet with useful data for this problem & post it up to the board

2) Take a look at the vlookup function in the help files. Also take a look at index and match

3) search here for example of all thrtee functions to see how they might help

4) post back with more info...

paddy
 

scattered

New Member
Joined
Feb 24, 2002
Messages
3
This is the risk matrix.
Minimal Moderate Significant
Likely Medium High High
Possible Low Medium High
Unlikely Low Low Medium

These are the nine possible combinations from the above.

ProbabilityImpact Risk
Likely Significant High
Likely Moderate High
Likely Minimal Medium
Possible Significant High
Possible Moderate Medium
Possible Minimal Low
Unlikely Significant Medium
Unlikely Moderate Low
Unlikely Minimal Low

I want to calculate the risk value for any combination of probability and impact that appears in a table. I hope this clarifies the problem. Thanks.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
<pre>

This is the risk matrix.
Minimal Moderate Significant
Likely Medium High High
Possible Low Medium High
Unlikely Low Low Medium

These are the nine possible combinations from the above.

ProbabilityImpact Risk
Likely Significant High
Likely Moderate High
Likely Minimal Medium
Possible Significant High
Possible Moderate Medium
Possible Minimal Low
Unlikely Significant Medium
Unlikely Moderate Low
Unlikely Minimal Low


</pre>

...just so it's clearer
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Having a problem with the html maker, so this will have to be long hand:

1) With your table of possible risk combinations in a1:a10 (headings in row 1),

and a table of the form:<pre>
Minimal Moderate Significant
Likely
Possible
Unlikely</pre>

in cells a12:d15,

enter:

=INDEX($C$2:$C$10,MATCH($A13,IF($B$2:$B$10=B$12,$A$2:$A$10)),0)

in b13. This needs to be array entered (i.e. using control + shift + enter, not just enter). Then copy across & down to fill the table.

paddy
This message was edited by PaddyD on 2002-11-04 19:07
 

Forum statistics

Threads
1,144,124
Messages
5,722,620
Members
422,448
Latest member
AugyIA

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top