In statistics, the confidence interval is the range that a population parameter is likely to fall into, for a given probability.
E.g. For given population and a probability of 95%, the confidence interval is the range in which a population parameter is 95% likely to fall.
Note that the accuracy of the confidence interval relies on the population having a normal distribution.
The Confidence Interval is discussed further on the Wikipedia Confidence Interval PageThe Excel Confidence.T function uses a Student's T-Distribution to calculate a confidence value that can be used to construct the confidence interval for a population mean, for a supplied probablity and supplied sample size. It is assumed that the standard deviation of the population is known.
The syntax of the Confidence.T function is:
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:
Note that the Confidence.T function is new to Excel 2010, so is not available in earlier versions of Excel. However, earlier versions of Excel do have the Confidence Function, which uses the Normal Distribution to calculate the confidence value.
In the spreadsheet below, the Excel Confidence.T 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.
| A | |
|---|---|
| 1 | =CONFIDENCE.T( 0.05, 0.07, 100 ) |
| 2 |
The above function returns a confidence value of 0.013889519.
Therefore the confidence interval is 1.8 ± 0.013889519, which is equal to:
For further details and examples of the Excel Confidence.T function, see the Microsoft Office website.
If you get an error from the Excel Confidence.T function this is likely to be one of the following:
| #NUM! | - |
Occurs if either:
|
| #DIV/0! | - | Occurs if the supplied size argument = 1. |
| #VALUE! | - | Occurs if any of the supplied arguments are non-numeric. |