Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 05, 02:52 PM posted to microsoft.public.excel.worksheet.functions
Rhapsody 1234
 
Posts: n/a
Default 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!

  #2   Report Post  
Old December 9th 05, 03:10 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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!

  #3   Report Post  
Old December 9th 05, 03:35 PM posted to microsoft.public.excel.worksheet.functions
John Michl
 
Posts: n/a
Default Convert letters into numbers. i.e. ABCD = 52

Very cool, Ron.

  #5   Report Post  
Old December 9th 05, 04:11 PM posted to microsoft.public.excel.worksheet.functions
Richard Buttrey
 
Posts: n/a
Default 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
__


  #6   Report Post  
Old December 9th 05, 04:40 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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
__

  #7   Report Post  
Old December 9th 05, 04:57 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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
__



  #8   Report Post  
Old December 9th 05, 08:21 PM posted to microsoft.public.excel.worksheet.functions
Sloth
 
Posts: n/a
Default 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
__

  #9   Report Post  
Old December 9th 05, 08:33 PM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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
__



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert numbers to text bellman Excel Discussion (Misc queries) 0 October 4th 05 10:28 PM
Auto convert an alphanumeric string (CIS9638S) to numbers only? SDesmond Excel Worksheet Functions 0 September 7th 05 01:17 AM
Convert text to numbers vipa2000 Excel Worksheet Functions 3 August 1st 05 09:01 PM
How do I sort letters before numbers in Excel? RiverGirl Excel Discussion (Misc queries) 4 May 27th 05 04:09 PM
How do I convert numbers stored as text with spaces to numbers Baffuor Excel Discussion (Misc queries) 1 May 24th 05 07:39 AM


All times are GMT +1. The time now is 01:59 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017