Bioruffo
Molecular Biology protocols, utilities and oddities
Green Web Hosting
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'-ATACGATGCATCGAT-3'") 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: triple-click 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:
>Tm_formula.xls< For Microsoft Excel 97/2003/2007
>Tm_formula.ods< For Open Office Calc 3
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 copy-pasted 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 copy-paste) 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 non-null 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).
 
Any special needs? Contact me!