KNOWLEDGEBASE - ARTICLE #1432

Using Excel to compute a confidence interval

GraphPad Prism and InStat compute confidence intervals as part of most analyses. But sometimes you may need to compute a confidence interval yourself. In most cases, a confidence interval equals the parameter plus or minus a margin of error which is computed as the standard error times the appropriate critical value from the t distribution.

You can obtain this critical t value using this free web GraphPad QuickCalc. Note that if you want 95% confidence, you need to enter 0.05. If you want 99% confidence, enter 0.01.

If you want to compute confidence intervals using Excel, the critical value of t  for a specified number of degrees of freedom (df) and confidence (C, in percent, usually 95) is computed using this formula:

TINV (1 – 0.01 * C, DF)

The confidence interval of the mean is centered on the sample mean and extends in each direction a distance equal to that critical value of t times the standard error of the mean (SEM). The SEM equals the sample SD divided by the square root of sample size.

Beware of the Confidence function built in to Excel.  It computes a confidence interval of a mean from a mean, SD and sample size. But note a big problem. It assumes that the SD you enter is the true SD of the entire population. In almost all cases, you don' t know the population SD. What you know is the SD of your sample, which may be larger or smaller than the true population SD. You can't know.

Prism (and InStat and QuickCalc) compute the confidence interval from the sample SD (that you entered or is computed from the data). The confidence interval computed this way is wider than the one computed by Excel's confidence function. With huge data sets, the discrepancy is tiny. With tiny data sets, the discrepancy is huge.

Details: Computing a confidence interval from the sample mean, sample SD and n requires using the t distribution, which Prism uses. Computing a confidence interval from the sample mean, the population SD and n uses the z (Gaussian) distribution. That is what Excel uses. The critical value of the t distribution depends on sample size. The critical value of z does not depend on sample size. When the sample size is huge, the t distribution approximates the z distribution.

Explore the Knowledgebase

Analyze, graph and present your scientific work easily with GraphPad Prism. No coding required.