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

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

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
-----Original Message-----
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
.

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 he

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

Dave Cadey wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

or even MATCH

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

--

HTH

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


"Ann Shaw" wrote in message
...
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
-----Original Message-----
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
.

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?

"Debra Dalgleish" wrote:

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 he

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

Dave Cadey wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html