Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert letters into numbers. i.e. ABCD = 52
Very cool, Ron.
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert letters into numbers. i.e. ABCD = 52
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert numbers to text | Excel Discussion (Misc queries) | |||
Auto convert an alphanumeric string (CIS9638S) to numbers only? | Excel Worksheet Functions | |||
Convert text to numbers | Excel Worksheet Functions | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) |