How to Use the CONFIDENCE Function in Excel

By Stephen L. Nelson, E. C. Nelson

The CONFIDENCE.NORM and CONFIDENCE.T functions in Excel calculate a value that you can use to create a confidence intervals for population means based on the sample mean. These definitions amount to a mouthful, but in practice what these functions do is straightforward.

Suppose that, based on a sample, you calculate that the mean salary for a chief financial officer for a particular industry equals $100,000. You might wonder how close this sample mean is to the actual population mean. Specifically, you might want to know what range of salaries, working at a 95-percent confidence level, includes the population mean.

The CONFIDENCE.NORM function calculates the number that you use to create this interval using the syntax

=CONFIDENCE.NORM(alpha,standard_dev,size)

where alpha equals 1 minus the confidence level, standard_dev equals the standard deviation of the population, and size equals the number of values in your sample.

If the standard deviation for the population equals $20,000 and the sample size equals 100, use the formula

=CONFIDENCE.NORM(1-.95,20000,100)

The function returns the value $3920 (rounded to the nearest dollar). This interval suggests that if the average chief financial officer’s salary in your sample equals $100,000, there’s a 95-percent chance that the population mean of the chief financial officers’ salaries falls within the range $96,080 to $103,920.

The CONFIDENCE.T function works in roughly the same way arguments do, but uses a Student T-distribution rather than a normal distribution. The CONFIDENCE.T function uses the following syntax:

=CONFIDENCE.T(alpha,standard_dev,size)

where alpha equals 1 minus the confidence level, standard_dev equals the standard deviation of the population, and size equals the number of values in your sample.

If the standard deviation for the population equals $20,000 and the sample size equals 100, use the formula

=CONFIDENCE.T(1-.95,20000,100)

The function returns the value $3968 (rounded to the nearest dollar). This interval suggests that if the average chief financial officer’s salary in your sample equals $100,000, there’s a 95-percent chance that the population mean of the chief financial officers’ salaries falls within the range $96,032 to $103,968.