Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a cell equal to another cells value and not it's fo. | Excel Worksheet Functions | |||
make cell contents equal to null value - not blank, but empty | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions |