By Stephen Nelson
Confidence intervals often give you useful insights into data sets you’re trying to better understand. A confidence interval is the interval around a sample mean into which you expect the population mean to fall a certain percentage of the time.
If you have a sample of size n and know the sample mean m and population standard deviation sigma (s), you can find the range into which the actual population mean will fall x% of the time. Common confidence levels are 90%, 95%, and 99%.
Using the Confidence Function
The CONFIDENCE function uses the following syntax:
Alpha is the significance level. It equals 1 minus the confidence level (expressed as a decimal). The s argument is the standard deviation of the data set. The n argument gives the number of items in the sample.
An Example Confidence Interval Calculation
For example, if a sample of 500 college graduates shows that they owe an average of $12,000 in student loans at graduation and the population standard deviation is $2,000, you can find a 95% confidence interval estimate of the population mean amount owed.
To do this using the CONFIDENCE function, enter alpha .05 as the first argument, the standard deviation 2000 as the second argument, and n 500 as the third argument. The function looks like this:
The function returns the value 175.30. So you can say with 95% confidence that the population mean is $12,000 plus or minus $175.30. (Note that if you have Microsoft Excel installed on your computer, you can copy the text shown above and paste the text into an Excel cell to make the calculation on your computer.)
One final note should be made here: If the value of the population standard deviation is unknown, you can use the value of the sample standard deviation as the point estimate of the population standard deviation.
About the author: Seattle CPA Stephen L. Nelson wrote the bestselling book, MBA’s Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits the S Corporations Explained and LLCs Explained websites.
Want to read more about MS Excel tips and tutorials? Visit Hot Excel.