IF Function

[Guest]

They wouldn't tell me everything the first go-round would they. Thanks boss!

So they actually are looking with a matrix. So D6=Detectiblity, E6=Effect
and F6=Probability. Is there a formula to determine all of these and then
find the total?

Effect(side) Probability(top)
High Mod Low High Mod Low High Mod Low
High 27 18 9 18 12 6 9 6 3
Mod 18 12 6 12 8 4 6 4 2
Low 9 6 3 6 4 2 3 2 1
Low Moderate High
Detectability

 

[Guest]

could you combine your question in one thread, I am getting a little lost
jumping back and forth.

Thanks--
Jeff

 

[Guest]

This was the original:
I am getting error messages for this:

=IF(D6=H,"27",IF(D6=M,"18", IF(D6=L,"9")))

I am trying to assign a value to a certain letter (High, Medium, Low). Is
this the wrong formula, or where do I need to make changes?

And I got a response, but then realized that results are a matrix, so there
are a total of 27 options. So if D6=H then E6 H=9, M=6, L=3 (and so on)

Is there a formula to determine all of these and then
find the total?

Effect(side) Probability(top)
High Mod Low High Mod Low High Mod Low
High 27 18 9 18 12 6 9 6 3
Mod 18 12 6 12 8 4 6 4 2
Low 9 6 3 6 4 2 3 2 1
Low Moderate High
Detectability

 

[Bob I]

It is merely the replacement of E and P and D being L,M,H = 1,2,3
then Multiply E*P*D=result

 

[Guest]

I'm sorry, could you clarify a little more? I'm not awesome at these things.

 

[Peo Sjoblom]

Create a list (you can hide it later) with all the letters and their
respective values

H 9
M 6
L 3


and so on, assume you put the list in S1:T27


then in E6 use


=IF(D6="","",VLOOKUP(D6,S1:T27,2,0))

 

[Bob I]

Ok let's say D6 holds the "Delectability" letter, E6 holds the "Effect"
letter, and F6 has the "Probability" letter. Since L,M,and H are
mathematically equal to 1,2 and 3 in this matrix we convert to a number
using FIND formula. Do that for the 3 parameters and multiply. So the
"result" cell would contain the following to generate the desired total.

=FIND(D6,"LMH",1)*FIND(E6,"LMH",1)*FIND(F6,"LMH",1)

You will need to use Upper case in this instance but lower case could be
accommodated with more expressions.