I am working with a Risk List. I have a column called Probability of
Occurrence and another column called Impact if Occurs. Both columns are
populated with L, M, or H (for Low, Medium and High).

What I want to do is to create a third column called Risk Score. To
determine the Risk Score I need to assign numerical values to L, M, and H in
the Probability and Impact columns and then multiply the two columns
together.

So, for example if I have a risk that has a low probabilty of occurrence (L
= 1) but will have a high impact if it occurs (H=9), I want the Risk Score
column to show a 9.

I have tried playing around with the IF statement, and I know it can be
nested as shown in Excel help, but how would I write the statement to do IF
statements for 2 separate columns and then multiply those columns together?
Is this even possible?

--
Thanks,
Carolyn G, PMP

Assuming the score is the product of the two factors and that
L is 1
M is 5
H is 9

then:

=((A1="L")+(A1="M")*5+(A1="H")*9)*((B1="L")+(B1="M ")*5+(B1="H")*9)

--
Gary''s Student - gsnu200742


"Carolyn G" wrote:

I am working with a Risk List. I have a column called Probability of
Occurrence and another column called Impact if Occurs. Both columns are
populated with L, M, or H (for Low, Medium and High).

What I want to do is to create a third column called Risk Score. To
determine the Risk Score I need to assign numerical values to L, M, and H in
the Probability and Impact columns and then multiply the two columns
together.

So, for example if I have a risk that has a low probabilty of occurrence (L
= 1) but will have a high impact if it occurs (H=9), I want the Risk Score
column to show a 9.

I have tried playing around with the IF statement, and I know it can be
nested as shown in Excel help, but how would I write the statement to do IF
statements for 2 separate columns and then multiply those columns together?
Is this even possible?

--
Thanks,
Carolyn G, PMP

One way ..

Assume the probabilities for both Prob Of Occ and Impact of Occ are
identical, viz.:

L 0.5
M 0.25
H 0.25

Assume the risks (L,M,H) for Prob Of Occ and Impact of Occ
are listed in A2:B2 down

Put in C2:
=IF(COUNTA(A2:B2)=2,VLOOKUP(A2,{"L",0.5;"M",0.25;" H",0.25},2,0)*VLOOKUP(B2,{"L",0.5;"M",0.25;"H",0.2 5},2,0),"")
Copy C2 down to return the risk scores. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carolyn G" wrote:
I am working with a Risk List. I have a column called Probability of
Occurrence and another column called Impact if Occurs. Both columns are
populated with L, M, or H (for Low, Medium and High).

What I want to do is to create a third column called Risk Score. To
determine the Risk Score I need to assign numerical values to L, M, and H in
the Probability and Impact columns and then multiply the two columns
together.

So, for example if I have a risk that has a low probabilty of occurrence (L
= 1) but will have a high impact if it occurs (H=9), I want the Risk Score
column to show a 9.

I have tried playing around with the IF statement, and I know it can be
nested as shown in Excel help, but how would I write the statement to do IF
statements for 2 separate columns and then multiply those columns together?
Is this even possible?

--
Thanks,
Carolyn G, PMP

As the probabilities for "Impact of Occ" are more likely to be opposite to
that for "Prob Of Occ", rather than identical (sorry about that)

You could try instead in C2, something like this:
=IF(COUNTA(A2:B2)=2,VLOOKUP(A2,{"L",0.5;"M",0.25;" H",0.25},2,0)*VLOOKUP(B2,{"L",0.25;"M",0.25;"H",0. 5},2,0),"")
where in A2 down = Prob Of Occ, in B2 down = Impact of Occ

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---