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, |
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, |
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 |
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, |
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 |
All times are GMT +1. The time now is 11:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com