ExcelBanter - IF Function

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - IF Function (https://www.excelbanter.com/excel-discussion-misc-queries/142996-if-function.html)

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

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

Thanks--
Jeff

"Janessa" wrote:

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

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

"Jeff" wrote:

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

Thanks--
Jeff

"Janessa" wrote:

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

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

Janessa wrote:

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

"Jeff" wrote:

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

Thanks--
Jeff

"Janessa" wrote:

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

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

"Bob I" wrote:

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

Janessa wrote:

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

"Jeff" wrote:

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

Thanks--
Jeff

"Janessa" wrote:

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

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))

--
Regards,

Peo Sjoblom

"Janessa" wrote in message
...
I'm sorry, could you clarify a little more? I'm not awesome at these
things.

"Bob I" wrote:

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

Janessa wrote:

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

"Jeff" wrote:

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

Thanks--
Jeff

"Janessa" wrote:

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

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.

Janessa wrote:

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

"Bob I" wrote:

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

Janessa wrote:

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

"Jeff" wrote:

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

Thanks--
Jeff

"Janessa" wrote:

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