trouble with an if stmt

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

trying to make an if stmt that takes codes I have entered in a column and gives them values
Any help would be appreciated!!
 
Hi
you may provide some more details (e.g. give an example, describe your
expected result, etc.). Otherwise all answers are just a guess :-)
 
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.
 
=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



elena said:
Thanks, Frank
I have a column that I will be placing codes in. the codes are E, L, C,
Click to expand...
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

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



Elena said:
if i use the lookup formula can i have it say "error" instead of n/a?
Click to expand...
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
Click to expand...
it!!!!!!!
 
Back
Top