The Excel CONFIDENCE Function

Confidence and Confidence.Norm Functions

In Excel 2010, the CONFIDENCE function has been replaced by the Confidence.Norm function.

Although it has been replaced, current versions of Excel still have the Confidence function (stored within the list of compatibility functions), to allow compatibility with earlier versions of Excel.

However, the Confidence function may not be available in future versions of Excel, so it is advised that you use the Confidence.Norm function if possible.

Function Description

The Excel Confidence function uses a Normal Distribution to calculate a confidence value that can be used to construct the Confidence Interval for a population mean, for a supplied probablity and sample size. It is assumed that the standard deviation of the population is known.

The syntax of the function is:

CONFIDENCE( alpha, standard_dev, size )

where the arguments are as follows:

alpha -

The significance level (= 1 - confidence level).

(E.g. a significance level of 0.05 equates to a confidence level of 95%).
standard_dev - The standard deviation of the population.
size - The population sample size.

To calculate the confidence interval for a population mean, the returned Confidence value must then be added to, and subtracted from, the sample mean. I.e. for the sample mean x:

Confidence Interval =   x   ±   CONFIDENCE

Confidence Function Example

In the spreadsheet below, the Excel Confidence Function is used to calculate the confidence interval with a significance of 0.05 (i.e. a confidence level of 95%), for the mean of a sample of heights of 100 men. The sample mean is 1.8 meters and the standard deviation is 0.07 meters.

1 =CONFIDENCE( 0.05, 0.07, 100 )

The above function returns a confidence value of 0.013719748

Therefore the confidence interval is 1.8 ± 0.013719748, which is equal to the range 1.786280252 to 1.813719748

For further details and examples of the Excel Confidence function, see the Microsoft Office website.

Confidence Function Common Errors

If you get an error from your Excel Confidence function this is likely to be one of the following:

Common Errors
#NUM! -

Occurs if either:

  • The supplied alpha is ≤ 0 or ≥ 1;
  • The supplied standard_dev is ≤ 0;
  • The supplied size argument is < 1.
#VALUE! - Occurs if any of the supplied arguments is non-numeric.