software

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,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=quot;quot;,quot;quot;,VLOOKUP(A1,'Sheet2'!A1:B26,2,0))--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;legmanquot; gt; wrote in message
...
gt;I need some help. I want to make a simple substitution formula. I need to
gt; assign a numerical value for each letter of the alphabet starting with the
gt; number 6. For example A = 6, B = 12, C = 18 and so on. I then could type
gt; a
gt; single letter into a cell and then in an adjacent cell display the number.
gt; The letters would be in one column, the numbers in the next. At the end
gt; of
gt; the displayed numbers, I would then total the numbers to receive value for
gt; the letters. I have tried IF statements, but those are too long, and
gt; MATCH,
gt; but I could not get it to work. Any ideas are appreciated.
gt; Thanks,
gt;

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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=541855Thanks, I will give this a try.

quot;Peo Sjoblomquot; wrote:

gt; Us a vlookup formula and a table, put all your letters in a column
gt;
gt; A 6
gt; B 12
gt; C 18
gt; etc
gt;
gt; assume the table is hidden in Sheet2 A1:B26
gt; and A1 is the cell you want to type the letter and B1 where you want the
gt; number
gt;
gt; in B1 put
gt;
gt; =IF(A1=quot;quot;,quot;quot;,VLOOKUP(A1,'Sheet2'!A1:B26,2,0))
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Excel 95 - Excel 2007
gt; Northwest Excel Solutions
gt; www.nwexcelsolutions.com
gt; quot;It is a good thing to follow the first law of holes;
gt; if you are in one stop digging.quot; Lord Healey
gt;
gt;
gt; quot;legmanquot; gt; wrote in message
gt; ...
gt; gt;I need some help. I want to make a simple substitution formula. I need to
gt; gt; assign a numerical value for each letter of the alphabet starting with the
gt; gt; number 6. For example A = 6, B = 12, C = 18 and so on. I then could type
gt; gt; a
gt; gt; single letter into a cell and then in an adjacent cell display the number.
gt; gt; The letters would be in one column, the numbers in the next. At the end
gt; gt; of
gt; gt; the displayed numbers, I would then total the numbers to receive value for
gt; gt; the letters. I have tried IF statements, but those are too long, and
gt; gt; MATCH,
gt; gt; but I could not get it to work. Any ideas are appreciated.
gt; gt; Thanks,
gt; gt;
gt;
gt;
gt;

Thanks, I will give this a try.

quot;daddylonglegsquot; wrote:

gt;
gt; You could construct a table and use a VLOOKUP formula or, alternatively,
gt; if you have a consistent pattern as per your example then with letter in
gt; A1 use this formula in B1
gt;
gt; =(CODE(UPPER(A1))-64)*6
gt;
gt; this would give you 6 for A, 12 for B etc.
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=541855
gt;
gt;