trouble with an if stmt

[elena]

trying to make an if stmt that takes codes I have entered in a column and gives them values
Any help would be appreciated!!

 

[Frank Kabel]

Hi
you may provide some more details (e.g. give an example, describe your
expected result, etc.). Otherwise all answers are just a guess

 

[elena]

Thanks, Fran
I have a column that I will be placing codes in. the codes are E, L, C, and M. The values are E and L = .1, C= .01 and M= .001. the formula i need is if cell = E then .1, if cell =L then .1, if cell = C, then .01, if cell = M then .001.

 

[elena]

and if false then "error"

 

[CLR]

=IF(A1="","",IF(OR(A1="E",A1="L"),0.1,IF(A1="C",0.01,IF(A1="M",0.001,"error"
))))

Vaya con Dios,
Chuck, CABGx3

Thanks, Frank
I have a column that I will be placing codes in. the codes are E, L, C,

and M. The values are E and L = .1, C= .01 and M= .001. the formula i need
is if cell = E then .1, if cell =L then .1, if cell = C, then .01, if cell =
M then .001.

 

[elena]

Thank you soooo much, you are a life saver!!! You saved me hours!!
Elena

 

[Leo Heuser]

Elena

If you run out of IF's (max 7 nested) here is an alternative to
Chuck's solution:

=HLOOKUP(A1,{"","E","L","C","M";"",0.1,0.1,0.01,0.001},2,0)

If A1 holds a value not in the list, the formula
returns the #N/A error.

 

[Elena]

if i use the lookup formula can i have it say "error" instead of n/a? CLR, I had some trouble with your formula, This is actually the formula I need except that It give an #value instead of error for anything but e,l,m,c. with those it works!
=ROUND(IF(E8="","",IF(OR(E8="E",E8="L"),0.1,IF(E8="C",0.01,IF(E8="M",0.001,"ERROR"))))*D8*C8,2)
The D8 and C8 are two numbers I have to multiply the .1, etc by.
This formula works as far as "error" is concerned I just need more in there!
=IF(E10="","",IF(OR(E10="E",E10="L"),0.1,IF(E10="C",0.01,IF(E10="M",0.001,"ERROR"))))
If anyone understands this and can help i would really appreciate it!!!!!!!

 

[JE McGimpsey]

Perhaps:

Make a lookup table. For instance, put E,L,C,M in J1:J4 and
0.1,0.1,0.01,0.001 in K1:K4

Then enter

=IF(E10="","", IF(ISNA(MATCH(E10,J1:J4,FALSE)), "ERROR",
VLOOKUP(E10,J1:K4,2,FALSE)))

 

[CLR]

I'm sure this can be shortened, but it seems to work..........

=IF(ISERR(ROUND(IF(E8="","",IF(OR(E8="E",E8="L"),0.1,IF(E8="C",0.01,IF(E8="M
",0.001,"ERROR"))))*D8*C8,2)),"error",ROUND(IF(E8="","",IF(OR(E8="E",E8="L")
,0.1,IF(E8="C",0.01,IF(E8="M",0.001,"ERROR"))))*D8*C8,2))


Vaya con Dios,
Chuck, CABGx3

if i use the lookup formula can i have it say "error" instead of n/a?

CLR, I had some trouble with your formula, This is actually the formula I
need except that It give an #value instead of error for anything but
e,l,m,c. with those it works!

=ROUND(IF(E8="","",IF(OR(E8="E",E8="L"),0.1,IF(E8="C",0.01,IF(E8="M",0.001,"
ERROR"))))*D8*C8,2)
The D8 and C8 are two numbers I have to multiply the .1, etc by.
This formula works as far as "error" is concerned I just need more in there!
=IF(E10="","",IF(OR(E10="E",E10="L"),0.1,IF(E10="C",0.01,IF(E10="M",0.001,"E
RROR"))))
If anyone understands this and can help i would really appreciate

it!!!!!!!