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!

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!

Convert letters into numbers. i.e. ABCD = 52
 

Very cool, Ron.

Convert letters into numbers. i.e. ABCD = 52
 

On Fri, 9 Dec 2005 05:52:01 -0800, "Rhapsody 1234" <Rhapsody
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!

Just as a matter of interest, what are the values that you want to
apply to A,C,G & T?

Aren't the ASCII code sums going to be non unique.
i.e CCCCCC & AAAAGG both sum to 204

In which case aren't you going to find problems with any numerical
analysis?

Rgds




Richard Buttrey
__

Convert letters into numbers. i.e. ABCD = 52
 

That's quite brilliant.

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

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
__

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

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
__

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

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
__

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

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
__

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

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
__