Remember Me? December 9th 05, 01:52 PM posted to microsoft.public.excel.worksheet.functions
 Rhapsody 1234 Posts: n/a Convert letters into numbers. i.e. ABCD = 52

I work with DNA and would like to assign numerical values to the bases. i.e.
A = 32 etc.
Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then
have a numerical display of the value obtained if these are summed. e.g. if
A=32, AA=64, AAA=96 etc.

Any way I can do this?
Thanks! December 9th 05, 02:10 PM posted to microsoft.public.excel.worksheet.functions
 Ron Coderre Posts: n/a Convert letters into numbers. i.e. ABCD = 52

Try this:
For a DNA sequence in Cell A1
B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-33)

Does that help?

***********
Regards,
Ron

"Rhapsody 1234" wrote:

I work with DNA and would like to assign numerical values to the bases. i.e.
A = 32 etc.
Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then
have a numerical display of the value obtained if these are summed. e.g. if
A=32, AA=64, AAA=96 etc.

Any way I can do this?
Thanks! December 9th 05, 02:35 PM posted to microsoft.public.excel.worksheet.functions
 John Michl Posts: n/a Convert letters into numbers. i.e. ABCD = 52

Very cool, Ron. December 9th 05, 02:47 PM posted to microsoft.public.excel.worksheet.functions
 Richard Buttrey Posts: n/a Convert letters into numbers. i.e. ABCD = 52 December 9th 05, 03:11 PM posted to microsoft.public.excel.worksheet.functions
 Richard Buttrey Posts: n/a Convert letters into numbers. i.e. ABCD = 52

That's quite brilliant.

Would you mind explaining (in English) how this sumproduct is working

I thought sumproduct needed at least a couple of arrays, but this only
appears to have one.

Rgds

On Fri, 9 Dec 2005 06:10:03 -0800, "Ron Coderre"
wrote:

Try this:
For a DNA sequence in Cell A1
B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-33)

Does that help?

***********
Regards,
Ron

"Rhapsody 1234" wrote:

I work with DNA and would like to assign numerical values to the bases. i.e.
A = 32 etc.
Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then
have a numerical display of the value obtained if these are summed. e.g. if
A=32, AA=64, AAA=96 etc.

Any way I can do this?
Thanks!

Richard Buttrey
__ December 9th 05, 03:40 PM posted to microsoft.public.excel.worksheet.functions
 Ron Coderre Posts: n/a Convert letters into numbers. i.e. ABCD = 52

Thanks, but....I'm really only letting Excel do its job.

Here's the explanation (Kinda long though...I figured maybe too much info is
better than not enough, in this case):

=SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-33)

I want the ASCII code for each letter in the string. To do that, I need to
create a array that contains each of those letters. The MID function can
extract subsets of strings.

=MID("MYWORD",2,1) extracts a 1-letter string from MYWORD, beginning with
the 2nd character. It returns "Y"

If I knew that the string was ALWAYS going to be 6 characters, I could
create the array by using: =MID("MYWORD",{1,2,3,4,5,6},1)
That would return {"M","Y","W","O","R","D"}

I could also write that formula as: =MID("MYWORD",ROW(1:6),1)
Note: the ROW(1:6) Returns the array of row numbers associated with rows
1:6....{1,2,3,4,5,6}

But, since I don't know how long the text will be, I need to create a
dynamic array of numbers from 1 to ???, that is driven by the string length.
Consequently, I used the INDIRECT function...which tries to convert its
contents into any kind of Excel range reference.

So if A1 contains "MYWORD", ROW(INDIRECT("1:"&LEN(A1))) creates a string
"1:6", which INDIRECT converts to references to those rows (1:6), for which
the ROW function returns the array of row numbers: {1,2,3,4,5,6}

This formula: MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) returns the array of
letters.

I use the UPPER function to bring consistency to the array:
the code for "A" is 32, but the code for "a" is 95! Which brings us to the
CODE function, which returns the ASCII code for a given letter.

The codes for MYWORD are {77;89;87;79;82;68}
The OP indicated that A should equate to 32, but the code for A is 65. So I
had to subtract 33 from it to get to 32. The new array is:
{44;56;54;46;49;35}

Last, the SUMPRODUCT returns the sum of the values in that array: 284

I hope that helps

***********
Regards,
Ron

"Richard Buttrey" wrote:

That's quite brilliant.

Would you mind explaining (in English) how this sumproduct is working

I thought sumproduct needed at least a couple of arrays, but this only
appears to have one.

Rgds

On Fri, 9 Dec 2005 06:10:03 -0800, "Ron Coderre"
wrote:

Try this:
For a DNA sequence in Cell A1
B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-33)

Does that help?

***********
Regards,
Ron

"Rhapsody 1234" wrote:

I work with DNA and would like to assign numerical values to the bases. i.e.
A = 32 etc.
Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then
have a numerical display of the value obtained if these are summed. e.g. if
A=32, AA=64, AAA=96 etc.

Any way I can do this?
Thanks!

Richard Buttrey
__ December 9th 05, 03:57 PM posted to microsoft.public.excel.worksheet.functions
 Peo Sjoblom Posts: n/a Convert letters into numbers. i.e. ABCD = 52

It's a known technique used in many ways, here's another from
Bob Phillips basically using the same technique to get something else

http://tinyurl.com/9oh4n

--

Regards,

Peo Sjoblom

"Richard Buttrey" wrote in
message ...
That's quite brilliant.

Would you mind explaining (in English) how this sumproduct is working

I thought sumproduct needed at least a couple of arrays, but this only
appears to have one.

Rgds

On Fri, 9 Dec 2005 06:10:03 -0800, "Ron Coderre"
wrote:

Try this:
For a DNA sequence in Cell A1
B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-33)

Does that help?

***********
Regards,
Ron

"Rhapsody 1234" wrote:

I work with DNA and would like to assign numerical values to the bases.

i.e.
A = 32 etc.
Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and

then
have a numerical display of the value obtained if these are summed.

e.g. if
A=32, AA=64, AAA=96 etc.

Any way I can do this?
Thanks!

Richard Buttrey
__ December 9th 05, 07:21 PM posted to microsoft.public.excel.worksheet.functions
 Sloth Posts: n/a Convert letters into numbers. i.e. ABCD = 52

Why do you use SUMPRODUCT instead of SUM?

"Ron Coderre" wrote:

Thanks, but....I'm really only letting Excel do its job.

Here's the explanation (Kinda long though...I figured maybe too much info is
better than not enough, in this case):

=SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-33)

I want the ASCII code for each letter in the string. To do that, I need to
create a array that contains each of those letters. The MID function can
extract subsets of strings.

=MID("MYWORD",2,1) extracts a 1-letter string from MYWORD, beginning with
the 2nd character. It returns "Y"

If I knew that the string was ALWAYS going to be 6 characters, I could
create the array by using: =MID("MYWORD",{1,2,3,4,5,6},1)
That would return {"M","Y","W","O","R","D"}

I could also write that formula as: =MID("MYWORD",ROW(1:6),1)
Note: the ROW(1:6) Returns the array of row numbers associated with rows
1:6....{1,2,3,4,5,6}

But, since I don't know how long the text will be, I need to create a
dynamic array of numbers from 1 to ???, that is driven by the string length.
Consequently, I used the INDIRECT function...which tries to convert its
contents into any kind of Excel range reference.

So if A1 contains "MYWORD", ROW(INDIRECT("1:"&LEN(A1))) creates a string
"1:6", which INDIRECT converts to references to those rows (1:6), for which
the ROW function returns the array of row numbers: {1,2,3,4,5,6}

This formula: MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) returns the array of
letters.

I use the UPPER function to bring consistency to the array:
the code for "A" is 32, but the code for "a" is 95! Which brings us to the
CODE function, which returns the ASCII code for a given letter.

The codes for MYWORD are {77;89;87;79;82;68}
The OP indicated that A should equate to 32, but the code for A is 65. So I
had to subtract 33 from it to get to 32. The new array is:
{44;56;54;46;49;35}

Last, the SUMPRODUCT returns the sum of the values in that array: 284

I hope that helps

***********
Regards,
Ron

"Richard Buttrey" wrote:

That's quite brilliant.

Would you mind explaining (in English) how this sumproduct is working

I thought sumproduct needed at least a couple of arrays, but this only
appears to have one.

Rgds

On Fri, 9 Dec 2005 06:10:03 -0800, "Ron Coderre"
wrote:

Try this:
For a DNA sequence in Cell A1
B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-33)

Does that help?

***********
Regards,
Ron

"Rhapsody 1234" wrote:

I work with DNA and would like to assign numerical values to the bases. i.e.
A = 32 etc.
Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then
have a numerical display of the value obtained if these are summed. e.g. if
A=32, AA=64, AAA=96 etc.

Any way I can do this?
Thanks!

Richard Buttrey
__ December 9th 05, 07:33 PM posted to microsoft.public.excel.worksheet.functions
 Ron Coderre Posts: n/a Convert letters into numbers. i.e. ABCD = 52

Actually, you CAN use the SUM in place of the SUMPRODUCT function.....but
you'll need to commit the resulting array formula by holding down
[Ctrl]+[Shift] when you press [Enter].

My general preference is to use SUMPRODUCT. Particularly, if the workbook
will be used by others. The reason: If somebody who is inexperienced with
array formulas edits the array formula....they'll never guess to
[Ctrl]+[Shift]+[Enter]. Invariably, I get the call that the workbook is
"broken". That problem is avoided by using the SUMPRODUCT function.

Does that make sense?

***********
Regards,
Ron

"Sloth" wrote:

Why do you use SUMPRODUCT instead of SUM?

"Ron Coderre" wrote:

Thanks, but....I'm really only letting Excel do its job.

Here's the explanation (Kinda long though...I figured maybe too much info is
better than not enough, in this case):

=SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-33)

I want the ASCII code for each letter in the string. To do that, I need to
create a array that contains each of those letters. The MID function can
extract subsets of strings.

=MID("MYWORD",2,1) extracts a 1-letter string from MYWORD, beginning with
the 2nd character. It returns "Y"

If I knew that the string was ALWAYS going to be 6 characters, I could
create the array by using: =MID("MYWORD",{1,2,3,4,5,6},1)
That would return {"M","Y","W","O","R","D"}

I could also write that formula as: =MID("MYWORD",ROW(1:6),1)
Note: the ROW(1:6) Returns the array of row numbers associated with rows
1:6....{1,2,3,4,5,6}

But, since I don't know how long the text will be, I need to create a
dynamic array of numbers from 1 to ???, that is driven by the string length.
Consequently, I used the INDIRECT function...which tries to convert its
contents into any kind of Excel range reference.

So if A1 contains "MYWORD", ROW(INDIRECT("1:"&LEN(A1))) creates a string
"1:6", which INDIRECT converts to references to those rows (1:6), for which
the ROW function returns the array of row numbers: {1,2,3,4,5,6}

This formula: MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) returns the array of
letters.

I use the UPPER function to bring consistency to the array:
the code for "A" is 32, but the code for "a" is 95! Which brings us to the
CODE function, which returns the ASCII code for a given letter.

The codes for MYWORD are {77;89;87;79;82;68}
The OP indicated that A should equate to 32, but the code for A is 65. So I
had to subtract 33 from it to get to 32. The new array is:
{44;56;54;46;49;35}

Last, the SUMPRODUCT returns the sum of the values in that array: 284

I hope that helps

***********
Regards,
Ron

"Richard Buttrey" wrote:

That's quite brilliant.

Would you mind explaining (in English) how this sumproduct is working

I thought sumproduct needed at least a couple of arrays, but this only
appears to have one.

Rgds

On Fri, 9 Dec 2005 06:10:03 -0800, "Ron Coderre"
wrote:

Try this:
For a DNA sequence in Cell A1
B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))-33)

Does that help?

***********
Regards,
Ron

"Rhapsody 1234" wrote:

I work with DNA and would like to assign numerical values to the bases. i.e.
A = 32 etc.
Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then
have a numerical display of the value obtained if these are summed. e.g. if
A=32, AA=64, AAA=96 etc.

Any way I can do this?
Thanks!

Richard Buttrey
__

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post bellman Excel Discussion (Misc queries) 0 October 4th 05 10:28 PM SDesmond Excel Worksheet Functions 0 September 7th 05 01:17 AM vipa2000 Excel Worksheet Functions 3 August 1st 05 09:01 PM RiverGirl Excel Discussion (Misc queries) 4 May 27th 05 04:09 PM Baffuor Excel Discussion (Misc queries) 1 May 24th 05 07:39 AM

All times are GMT +1. The time now is 08:23 PM. Copyright ©2004-2020 ExcelBanter.