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

Graphing Data - How To Calculate Error Bars

When turning numerical data into graphical form (e.g. bar graphs), you sometimes find that the data is much easier to interpret when changes observed in test conditions are presented as fold changes relative to the control, which is set to “1”. In doing this, you might wonder how you would go about calculating the error bars. In the following, I will detail how I go about calculating error bars for relative fold changes:

Below is an example data set with three control and three experimental replicates:

C1. 3.55

C2. 3.82

C3. 3.13

E1. 5.21

E2. 6.74

E3. 5.68

Take the average and you will have:

à C-AVG=3.50     E-AVG=5.88

Next, calculate the relative fold change by dividing by the control average:

à C-AVG=3.50/3.50   E-AVG=5.88/3.50  à  C-RelAVG=1   E-RelAVG=1.68

For the error bars, calculate the SEM:

C-SEM=0.20   E-SEM=0.45

Next calculate the SEM for the relative fold change values by dividing the SEMs with the control average:

à C-SEM=0.20/3.50            E-SEM=0.45/3.50  à  C-RelSEM=0.06   E-RelSEM=0.13

Below is an illustration of the data presented without setting the data relative to the control:

  

Here is the data set relative to the control:

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

Basic Fun

If you have ever wanted to automate your calculations to save on time Excel VBA can do wonders. Check this out.

Its very simple and very basic. The macros used in the video are below. Feel free to use it.

_________________________________________________________

Sub Average()

    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-2]:R[2]C[-2])"

    ActiveCell.Select

    Selection.Copy

    ActiveCell.Offset(3, 0).Range("A1").Select

    ActiveSheet.Paste

    ActiveCell.Offset(3, 0).Range("A1").Select

    ActiveSheet.Paste

    ActiveCell.Offset(3, 0).Range("A1").Select

    ActiveSheet.Paste

    

End Sub

_________________________________________________________

Sub SD()

    ActiveCell.FormulaR1C1 = "=STDEV(RC[-4]:R[2]C[-4])"

    ActiveCell.Select

    Selection.Copy

    ActiveCell.Offset(3, 0).Range("A1").Select

    ActiveSheet.Paste

    ActiveCell.Offset(3, 0).Range("A1").Select

    ActiveSheet.Paste

    ActiveCell.Offset(3, 0).Range("A1").Select

    ActiveSheet.Paste

End Sub

_________________________________________________________

Sub SEM()

    ActiveCell.FormulaR1C1 = "=RC[-2]/SQRT(3)"

    ActiveCell.Select

    Selection.Copy

    ActiveCell.Offset(3, 0).Range("A1").Select

    ActiveSheet.Paste

    ActiveCell.Offset(3, 0).Range("A1").Select

    ActiveSheet.Paste

    ActiveCell.Offset(3, 0).Range("A1").Select

    ActiveSheet.Paste

End Sub

_________________________________________________________

Sub ClearCalculations()

Range("d3:i12").Select

    Selection.clear

        

Cells(1, 1).Select

End Sub

_________________________________________________________

Sub Instructions()

MsgBox "Select the cell you want the calculations to start from then move the cursor to the button. Click and enjoy :D"

End Sub

_________________________________________________________