How do I get a letter in one cell ito equal a number in anotherl..

[Guest]

I wish to be able to key a letter of the alphabet in a cell ant get a number
to appear in another
eg Cell A1 could be A or B or or C with a=2, b=4 or c=7 etc
The answer should appear in say Cell B1 as 2, 4 or 7 according tp whay
letter has been keyed

 

[Ann Shaw]

Hi

I would use a Nested If function.

As follows:

A B
=if(A1="A",1,if(A1="B",2,if(A1="C",3,if
(A1="D",4,0))))

I cannot remember how many if's you can add but I have
used this with 6 conditions. You must put the false
option at the end 0 or "" if you want to leave the cell
blank. Remember to close as many brackets as you open.

Hope this helps - this is just the easy solution , I'm
sure that one of the Microsoft genius' will have another
way to do this.

Ann

 

[Debra Dalgleish]

You could create a table that lists the letters in one column and their
values in the adjacent column. Then use a VLOOKUP formula in cell B1, to
return the value for the letter in cell A1. There are instructions and
examples here:

http://www.contextures.com/xlFunctions02.html

 

[Bob Phillips]

or even MATCH

=MATCH(A1,{"","A","","B","","","C"},0)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I believe the lookup functions are limited to the number of entries (13),
could be wrong on that. I think this might be a bit easier. If column A
contains your "letter" entries, then the following formula will convert the
letter as you specified:

=char(A1) - 64

Since "A" is represented by ANSI code 65 and you want "A" to be equal to 1,
we need to substract 64 from the resulting code.

Make sense?