Matrix
Posted by Rayn on April 30, 2001 6:27 AM
Hi
i have a matrix that consistes of 4 columns (the columns state the risk catagorie) and 4 rows (the rows the expectancy of the risk )
(i am doing this for risk classification).
in one cell i will enter the catagorie and in another cell i will enter the expectancy manually. i would like a formula to give me the risk number or class from the matrix by reading the catagorie verses the expectancy. i am not sure if this is possible via a formula or a macro.
Your help will be greatly appreciated
Thank you!!!!!!!
Posted by Mark W. on April 30, 2001 6:48 AM
Rayn, some sample data would be helpful. Could
you provide one of your 4x4 matrices including
the 4 risk categories and risk expectancies?
An easy way to accomplish this is to select an
empy cell, type an equal sign, select you data,
copy the contents of the formula bar and paste it
into a follow-up posting.
Posted by Rayn on April 30, 2001 4:37 PM
Low Low - Moderate Moderate Moderate High
EXTREMELY UNLIKELY 1 2
UNLIKELY
LIKELY
VERY LIKELY
DEFINETLY
Posted by Mark W. on April 30, 2001 5:15 PM
Rayn, are these the right risk categories and
expectancies? Originally, you were describing
a 4x4 matrix, but this would be 5x5.
{"","Low","Low - Moderate","Moderate","Moderate High"
;"EXTREMELY UNLIKELY",0,0,0,0,0
;"UNLIKELY",0,0,0,0,0
;"LIKELY",0,0,0,0,0
;"VERY LIKELY",0,0,0,0,0
;"DEFINETLY",0,0,0,0,0}
Posted by Rayn on April 30, 2001 5:17 PM
Low Low - Moderate Moderate Moderate High High
EXTREMELY UNLIKELY 0 1 2 3 4
UNLIKELY 1 2 3 4 5
LIKELY 2 3 4 5 6
VERY LIKELY 3 4 5 6 7
DEFINETLY 4 5 6 7 8
I could e-mail you the spread sheet i am working on
Thank you for your help !!!!!!
Posted by Rayn on April 30, 2001 5:19 PM
Sorry but we had to add in an extra one at work Today
my first message was posted from home last night
Thank you
Posted by Mark W. on April 30, 2001 5:27 PM
Okay, then suppose...
That you matrix resides in cells A1:F6 and is
constructed as follows:
{"","Low","Low - Moderate","Moderate","Moderate","High"
;"EXTREMELY UNLIKELY",1,2,3,4,5
;"UNLIKELY",2,3,4,5,6
;"LIKELY",3,4,5,6,7
;"VERY LIKELY",4,5,6,7,8
;"DEFINETLY",5,6,7,8,9}
...then you could enter a risk category in cell H1,
an expectancy in cell H2, and use the following
formula:
=INDEX($B$2:$F$6,MATCH($H$2,$A$2:$A$6,0),MATCH($H$1,$B$1:$F$1,0))
If H1:H2 contained {"Moderate";"VERY LIKELY"} the
the formula's result would be 6. : Rayn, are these the right risk categories and
Low Low - Moderate Moderate Moderate High EXTREMELY UNLIKELY 1 2 UNLIKELY LIKELY VERY LIKELY DEFINETLY
Posted by Mark W. on April 30, 2001 5:33 PM
Alternately...
You could select cells B2:F6, choose the
Insert | Name | Create... menu command, check
the "Top row" and "Left column" boxes, press
[ OK ], and use the formula...
='Moderate' 'VERY LIKELY'
...to return the same value! That you matrix resides in cells A1:F6 and is
Posted by Mark W. on April 30, 2001 5:43 PM
Re: Alternately...
Actually, it's not necessary to create names as
described below. You can use ='Moderate' 'VERY LIKELY'
if your Tools | Options... Calculation setting
for "Accept labels in formulas" is checked. You could select cells B2:F6, choose the
Posted by Rayn on April 30, 2001 8:05 PM
Low Low - Moderate Moderate Moderate High EXTREMELY UNLIKELY 1 2 UNLIKELY LIKELY VERY LIKELY DEFINETLY
Thanks so much !! life saver!!!
