Solved

# Formula for a Matrix

Posted on 2013-05-27
244 Views
Hiya

I was wondering what formula would be useful for a matrix (please see attached).  For example, if the likelyhood is rare, and the consequence is insignificant then the answer would be L (cell E42).

Would anyone have a suggestion of the best formula to use?

Thanks.

Greg
Test.xls
0
Question by:greg_c
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 43

Expert Comment

ID: 39199018
=INDEX(D6:H10,MATCH("Rare",\$C\$6:\$C\$10,0),MATCH("Insignificant",\$D\$5:\$H\$5,0))
0

LVL 43

Expert Comment

ID: 39199020
If A1 and A2 contain the words "Rare" and "Insignificant" then you can use

=INDEX(D6:H10,MATCH(A1,\$C\$6:\$C\$10,0),MATCH(A2,\$D\$5:\$H\$5,0))
0

LVL 18

Expert Comment

ID: 39199059
use of index and match like this example
http://skp.mvps.org/xl00002.htm

will provide you with a sample file with your data shortly
0

LVL 18

Accepted Solution

xtermie earned 500 total points
ID: 39199066
If in the spreadsheet you have a list with the varibale combos
ie. Rows 11 and 12 below
A                       B                       C
Likelihood      Consequence
Rare                       Insignificant        L

Then in C12 the formula reads
=INDEX(\$D\$6:\$H\$10;MATCH(B12;\$C\$6:\$C\$10;0);MATCH(C12;\$D\$5:\$H\$5;0))

See attached sample file
Test-sample.xls
0

LVL 18

Expert Comment

ID: 39199070
your should "lock" the data range in the formula with absolute reference in order to be able to copy the formula properly.
0

Author Closing Comment

ID: 39202520
Thank you, and apologies for the delay in responding.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month6 days, 8 hours left to enroll