Solved

Formula for a Matrix

Posted on 2013-05-27
6
246 Views
Last Modified: 2013-05-28
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
Comment
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
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39199018
=INDEX(D6:H10,MATCH("Rare",$C$6:$C$10,0),MATCH("Insignificant",$D$5:$H$5,0))
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
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

by:xtermie
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 18

Accepted Solution

by:
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

by:xtermie
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

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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question