Please help...Microsoft Excel? HOW TO USE FORMULA?

Question

I asked this question a bit earlier, I would like to write a computer program to calculate percentage composition, or to gain access to one
that maybe exist already. This is what I want to determine for example, I have three groups of amino acids Polar = S and T, Non-polar V,A,L,G,P and Basic = R. If for example I have the sequence
SRVAL, is there a program I can write or use to make the determination as 20%polar, 20%basic and 60% non-polar.

I have perl installed on my computer, but I have no idea how to use it. Is there other programming software less complicated than perl, that I can use to write a program.

and I got this response


Open up Microsoft Excel, press Alt+F11. In the new "Microsoft Visual Basic" window that opens up right-click "Sheet 1", select "Insert", then "Module"

Now paste the following code in the window.

Function calcComposition(s)
s = UCase(s)
j = Len(s)
If j = 0 Then Exit Function
For i = 1 To j
Select Case Mid(s, i, 1)
Case "S", "T"
polar = polar + 1
Case "V", "A", "L", "G", "P"
nonpolar = nonpolar + 1
Case "R"
basic = basic + 1
End Select
Next
calcComposition = polar * 100 / j & "% polar, " & basic * 100 / j & "% basic and " & nonpolar * 100 / j & "% non-polar"
End Function

Now you can just type
=calcComposition("SRVAL")
in any cell of this workbook and get
20% polar, 20% basic and 60% non-polar
as answer.

=calcComposition("SRSVAL")
will answer with
33.3333333333333% polar, 16.6666666666667% basic and 50% non-polar

NOW MY QUESTION IS, I RIGHT CLICKED ON SHEET ONE AND THEN I CLICKED INSERT AND 'MODULE' DID NOT SHOW UP, ONLY 'WORKSHEET'. 'CHART' 'MS EXCEL MARCO' AND ' MS EXCEL 5.0 DIALOG'

HOW DO I GO ABOUT USING THE ABOVE FORMULA IN EXCEL?
THANKS

Amir S · Accepted Answer

You know what... if you can do something through Excel formulas, why use VBA?  So I suggest you forget about that piece of code you have and do the following:

Input your sequence in cell A1.  I suggest you label this cell SEQUENCE for more understandability of your spreadsheet.

Now, column B is going to count your letters for you.
In B1 enter this formula: =LEN(SEQUENCE)-LEN(SUBSTITUTE(SEQUENCE,"S",""))
In B2 enter this formula: =LEN(SEQUENCE)-LEN(SUBSTITUTE(SEQUENCE,"T",""))
In B3 enter this formula: =LEN(SEQUENCE)-LEN(SUBSTITUTE(SEQUENCE,"V",""))
In B4 enter this formula: =LEN(SEQUENCE)-LEN(SUBSTITUTE(SEQUENCE,"A",""))
In B5 enter this formula: =LEN(SEQUENCE)-LEN(SUBSTITUTE(SEQUENCE,"L",""))
In B6 enter this formula: =LEN(SEQUENCE)-LEN(SUBSTITUTE(SEQUENCE,"G",""))
In B7 enter this formula: =LEN(SEQUENCE)-LEN(SUBSTITUTE(SEQUENCE,"P",""))
In B8 enter this formula: =LEN(SEQUENCE)-LEN(SUBSTITUTE(SEQUENCE,"R",""))

Now we should calculate the totals of each category.  We'll do this in column C:
In C1 enter this formula: =SUM(B1:B2)
In C2 enter this formula: =SUM(B3:B8)
In C3 enter this formula: =B8

Now all we need is the values in column C (as well as the length of the string to calculate the percentages).  Let's say column D will contain our results:
In D1 enter this formula: =C1/LEN(SEQUENCE)
In D2 enter this formula: =C2/LEN(SEQUENCE)
In D3 enter this formula: =C3/LEN(SEQUENCE)

And to clarify things, in column E you put the explanation:
E1 should say Polar
E2 should say Non-Polar
E3 should say Basic

If you want the entire output to be in one sentence/cell instead of in 3 cells, you should use the CONCATENATE formula to concatenate the values in cells D1,E1,D2,E2,D3,and E3.

BIG NOTE:
If you wanted the processing of a single sequence to be done in ONE row, you can still use the above strategy.  However, instead of taking up B1 to B8, use A2 to A9.  Then C1 to C3 will be in A10 to A12, D's will be in A13 to A15 and E's in A16 to A18.  And the final concatenation results can be placed in A19.

Hope this helps.

Trending News

Please help...Microsoft Excel? HOW TO USE FORMULA?

1 Answer