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:

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.