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,

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.

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" <[email protected]> wrote in message
news:[email protected]...
>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,
>

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" <[email protected]> wrote in message
> news:[email protected]...
> >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,
> >
>
>
>

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
>
>