Calculating the Geometric Mean/Geomean of Housekeeping Genes from Real-Time PCR Data Using Excel

In real-time PCR, it is not uncommon for multiple housekeeping genes to be used for normalising data. Knowing how to calculate an average for your housekeeping genes will be useful regardless of whether you opt to carry out relative or absolute quantification of your gene of interest.

If you are using three or more housekeeping genes, you can calculate the geometric or geomean easily using Excel.

Cell Formulae

Column B: Gene 1 CT values from your qPCR run

Column C: Gene 2 CT values from your qPCR run

Column D: Housekeeping gene 1 CT values from your qPCR run

Column E: Housekeeping gene 2 CT values from your qPCR run

Column F: Housekeeping gene 3 CT values from your qPCR run

Column G: =GEOMEAN(D4:F4) and copy/paste formula down to G18

Delta-Delta CT/Comparative CT Method Of Calculating Real-Time PCR Data Using Excel

For those who prefer to calculate their real-time PCR or qPCR data using the DDCT (also referred to as comparative CT) method but want to be able to evaluate the calculation of the data step by step, the following set of Excel formulae will come in handy. The Columns referred to correspond to the columns in the figure below.

Cell Formulae

Column B: Gene 1 CT values from your qPCR run

Column C: Gene 2 CT values from your qPCR run

Column D: Housekeeping gene CT values from your qPCR run

Column E: =B4-D4, =B5-D5, etc

Column F: =C4-D4, =C5-D5, etc

Column G: =AVERAGE(E4:E6)

Column H: =AVERAGE(F4:F6)

Column I: =E4-$G$4, =E5-$G$4, etc

Column J: =F4-$H$4, =F5-$H$4, etc

Column K: =POWER(2,-I4),=POWER(2,-I5), etc

Column L: =POWER(2,-J4), =POWER(2,-J5), etc

Column M: =AVERAGE(K4:K6), =AVERAGE(K7:K9), etc

Column N: =AVERAGE(L4:L6), =AVERAGE(L7:L9), etc

Column O: =STDEV(K4:K6), =STDEV(K7:K9),etc

Column P: =STDEV(L4:L6), =STDEV(L7:L9), etc

Column Q: =O4/SQRT(3), =O7/SQRT(3), etc

Column R: =P4/SQRT(3), =P7/SQRT(3), etc