Assinging a numerical value to A,B,C,D answer

R

rbvetter

Is there a formula that allows me to define the value of an answer fo
example, A=4, B=3, C=2, D=1, E=0
 
C

crispbd

Given that
Cell A1 contains the question,
B1 Contains the answer (a,b,c,d,e....)


This Formula allows custom values:
=IF(B1="a",4,IF(B1="b",3,IF(B1="c",2,IF(B1="d",1,IF(B1="e",0,"Invalid")))))


A simpler method for doing something similar:

The following formula will return 0 for a, 1 for b, 2 for c, 3 for d
etc... for all letters
=CODE(LOWER(B1))-9
 
J

JulieD

Hi

you could use an IF e.g.
=IF(A1="A",4,IF(A1="B",3,IF(A1="C",2,IF(A1="D",1,IF(A1="E",0,"Not
valid")))))

or you could create a table with these values and use a VLOOKUP
assume your table is on sheet2 range A2:B6
set out like
A 4
B 3
etc
then on sheet 1 the formula would be
=VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)
which says, look up the value in A1, in the table on Sheet2 and return the
associated value from the second column of the table (ie column B) where
there is an exact match.

Hope this helps
Cheers
julieD
 
B

Bruce Sinclair

Hi

you could use an IF e.g.
=IF(A1="A",4,IF(A1="B",3,IF(A1="C",2,IF(A1="D",1,IF(A1="E",0,"Not
valid")))))

or you could create a table with these values and use a VLOOKUP
assume your table is on sheet2 range A2:B6
set out like
A 4
B 3
etc
then on sheet 1 the formula would be
=VLOOKUP(A1,Sheet2!$A$2:$B$6,2,0)
which says, look up the value in A1, in the table on Sheet2 and return the
associated value from the second column of the table (ie column B) where
there is an exact match.

Hope this helps
Cheers
julieD
Click to expand...

Or you could use named cells ? put 4 in a1; name a1 "B"; test then becomes
B=4 ... would work if you had small numbers of these :)




Bruce

------------------------------
Health nuts are going to feel stupid someday, lying in hospitals
dying of nothing.

-Redd Foxx


Caution ===== followups may have been changed to relevant groups
(if there were any)
 
Top