ASSIGN NUMERICAL VALUES FOR LETTERS in EXCEL 2003

[Guest]

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,

 

[Peo Sjoblom]

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

 

[daddylonglegs]

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.

 

[Guest]

Thanks, I will give this a try.

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

 

[Guest]

Thanks, I will give this a try.