Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ASSIGN NUMERICAL VALUES FOR LETTERS in EXCEL 2003
I need some help. I want to make a simple substitution formula. I need to
assign a numerical value for each letter of the alphabet starting with the number 6. For example A = 6, B = 12, C = 18 and so on. I then could type a single letter into a cell and then in an adjacent cell display the number. The letters would be in one column, the numbers in the next. At the end of the displayed numbers, I would then total the numbers to receive value for the letters. I have tried IF statements, but those are too long, and MATCH, but I could not get it to work. Any ideas are appreciated. Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ASSIGN NUMERICAL VALUES FOR LETTERS in EXCEL 2003
Us a vlookup formula and a table, put all your letters in a column
A 6 B 12 C 18 etc assume the table is hidden in Sheet2 A1:B26 and A1 is the cell you want to type the letter and B1 where you want the number in B1 put =IF(A1="","",VLOOKUP(A1,'Sheet2'!A1:B26,2,0)) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "legman" wrote in message ... I need some help. I want to make a simple substitution formula. I need to assign a numerical value for each letter of the alphabet starting with the number 6. For example A = 6, B = 12, C = 18 and so on. I then could type a single letter into a cell and then in an adjacent cell display the number. The letters would be in one column, the numbers in the next. At the end of the displayed numbers, I would then total the numbers to receive value for the letters. I have tried IF statements, but those are too long, and MATCH, but I could not get it to work. Any ideas are appreciated. Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ASSIGN NUMERICAL VALUES FOR LETTERS in EXCEL 2003
You could construct a table and use a VLOOKUP formula or, alternatively, if you have a consistent pattern as per your example then with letter in A1 use this formula in B1 =(CODE(UPPER(A1))-64)*6 this would give you 6 for A, 12 for B etc. -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=541855 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ASSIGN NUMERICAL VALUES FOR LETTERS in EXCEL 2003
Thanks, I will give this a try.
"Peo Sjoblom" wrote: Us a vlookup formula and a table, put all your letters in a column A 6 B 12 C 18 etc assume the table is hidden in Sheet2 A1:B26 and A1 is the cell you want to type the letter and B1 where you want the number in B1 put =IF(A1="","",VLOOKUP(A1,'Sheet2'!A1:B26,2,0)) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "legman" wrote in message ... I need some help. I want to make a simple substitution formula. I need to assign a numerical value for each letter of the alphabet starting with the number 6. For example A = 6, B = 12, C = 18 and so on. I then could type a single letter into a cell and then in an adjacent cell display the number. The letters would be in one column, the numbers in the next. At the end of the displayed numbers, I would then total the numbers to receive value for the letters. I have tried IF statements, but those are too long, and MATCH, but I could not get it to work. Any ideas are appreciated. Thanks, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ASSIGN NUMERICAL VALUES FOR LETTERS in EXCEL 2003
Thanks, I will give this a try.
"daddylonglegs" wrote: You could construct a table and use a VLOOKUP formula or, alternatively, if you have a consistent pattern as per your example then with letter in A1 use this formula in B1 =(CODE(UPPER(A1))-64)*6 this would give you 6 for A, 12 for B etc. -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=541855 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003, Convert EXISTING Worksheet Data to XML? | Excel Discussion (Misc queries) | |||
How do I open a Quattro Pro 7.0 file in Excel 2003 | Excel Discussion (Misc queries) | |||
trying to open an excel file in excel 2003 | Excel Discussion (Misc queries) | |||
Assign numerical values to text codes | Excel Worksheet Functions | |||
quattro pro converter Excel 2003 | New Users to Excel |