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

Data Analysis – T-Test vs Mann Whitney U

For those unfamiliar with statistics, it can often be confusing when deciding which test to apply to analyse data in order to determine whether changes observed are indeed statistically significant (i.e. p<0.05)

The following will provide some guidance on how to analyse data between two groups (e.g. placebo vs drug treatment, normal vs diseased, light vs dark, etc).

What Are The Differences?

The t-test is a test between population means. They are parametric tests and should only be applied to data that is normally distributed. In contrast, the Mann-Whitney U (MWU) test is a test of differences in medians as well as the shape and spread of the data. It is a non-parametric test that can be used as an alternative to the t-test on data that is not normally distributed. However, it should be noted that the MWU test can be applied to normally distributed data.

The number of data points or sample size can also affect the choice in tests. If you have large datasets that have a normal distribution, a t-test can be very powerful. But if you have a small number of data points (e.g. less than 6 data points), a MWU test would be preferable to a t-test since the data is unlikely to have a normal distribution.

What Do I Use?  

To determine which test to apply, you will first need to establish whether your experimental data is normally distributed. For illustrative purposes, a mock dataset (below) will be used. The dataset below is from two groups (normal vs diseased). The sample type is coded and blind to the experimenter.

There are a number of normality tests for you to choose from, but if you have a set of data with less than 2000 data points, as above, try using the Shapiro-Wilk normality test. The null hypothesis is that your data points belong to a normal distribution; reciprocally, your alternative hypothesis is that your data points do not belong to a normal distribution. If p<0.05, your data does not have a normal distribution. For the above dataset, the results of running the Shapiro-Wilk test is as follows:

n = 24

Mean = 66.33333333333333

SD = 21.25142791451429

W = 0.9437512788610762

Threshold (p=0.01) = 0.8840000033378601

Threshold (p=0.05) = 0.9160000085830688

Threshold (p=0.10) = 0.9300000071525574

Here, p>0.05 at all thresholds. Accordingly, the alternative hypothesis is rejected and we conclude that the data points have a normal distribution.

** The results above were calculated using an online Shapiro-Wilk calculator.   

For sample sizes greater than 2000, you can use the Kolmogorov-Smirnov test. If you prefer to visualize the data in graphical form, try using a normal probability plot or quantile-quantile plot.

Other Ways To Test Normality

Aside from the normality tests mentioned above, there are other normality tests available. For their description, please refer to the following link.  

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

_________________________________________________________