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!

Try this:
For a DNA sequence in Cell A1
B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(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!

Very cool, Ron.

On Fri, 9 Dec 2005 05:52:01 -0800, "Rhapsody 1234" <Rhapsody
1234@discussions.microsoft.com> 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
__

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"
<ronSKIPTHIScoderre@bigfoot.com> wrote:

>Try this:
>For a DNA sequence in Cell A1
>B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(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
__

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:"&LEN(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"
> <ronSKIPTHIScoderre@bigfoot.com> wrote:
>
> >Try this:
> >For a DNA sequence in Cell A1
> >B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(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
> __
>

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" <chaos.theory.nospam.removethis@zen.co.uk> wrote in
message news:6f7jp1h0hhvl51sc01urcpvp9e20pn14qr@4ax.com...
> 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"
> <ronSKIPTHIScoderre@bigfoot.com> wrote:
>
> >Try this:
> >For a DNA sequence in Cell A1
> >B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(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
> __

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:"&LEN(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"
> > <ronSKIPTHIScoderre@bigfoot.com> wrote:
> >
> > >Try this:
> > >For a DNA sequence in Cell A1
> > >B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(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
> > __
> >

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:"&LEN(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"
> > > <ronSKIPTHIScoderre@bigfoot.com> wrote:
> > >
> > > >Try this:
> > > >For a DNA sequence in Cell A1
> > > >B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(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
> > > __
> > >