An Excel or OpenOffice Calc formula that calculates the TM of an oligo
Would you like to add a Tm calculation in your Excel or OpenOffice primer database?
Here's a formula that will return the Tm of an oligo, calculated by the equation:
TM=0.41*(%GC)+69.3(650/length)
This formula will work on oligos wrote either in uppercase or lowercase, and in oligos with or without spaces (i.e. if you write your oligos as AAC ATC TGC ACA AAT GGA CA).
As it is, it will not compute oligos with other characters (e.g. "5'ATACGATGCATCGAT3'") or N's. It can be modified to exclude other characters like "5'"; this will however make it even longer, so it's not shown here.
How to add the formula in your spreadsheet:
If your version of Excel is in english, you can create an empty spreadsheet and copy this sequence in cell B1:
(Windows: tripleclick with your left button to select all the paragraph.)
=IF((LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(SUBSTITUTE(A1;" ";""));"C";"");"A";"");"T";"");"G";"")))>0;"N/C";41*((LEN(SUBSTITUTE(A1;" ";""))LEN(SUBSTITUTE(SUBSTITUTE(UPPER(SUBSTITUTE(A1;" ";""));"C";"");"G";"")))/LEN(SUBSTITUTE(A1;" ";"")))+69,3(650/LEN(SUBSTITUTE(A1;" ";""))))
If you have Excel or OpenOffice in a different language than english, you can download a spreadsheet with the formula:
In these two files the formula is in cell B6, and calculates the Tm of the oligo in B5.
Now you'll have in one cell (e.g. B1 or B6) a formula that can be copypasted anywhere in your favourite spreadhseet, and that will calculate the Tm of an oligo stored in the immediate left cell (cell A1 or B5).
If you need to refer to another cell, drag (not copypaste) both cells to the right places. I.e. if you want to calculate the Tm of an oligo stored in C2 and display the result in cell C4, drag the oligo sequence cell to C2 and the formula cell to C4. The program will automatically adjust the references to the correct distance between cells.
What the formula does:
This formula will compute oligo sequences composed of the characters A, a, C, c, T, t, G, g, and space. If it finds any character it can't compute, it will return a "N/C" message.
How does it work:
First it checks the oligo string for unsupported characters. It does it by deleting any A C T G a c t g and space, and verifying if there's anything left. If this results in a nonnull string, the formula returns a "N/C" message.
If the check is passed, the formula will perform the calculation TM=0.41*(%GC)+69.3(650/length).
