Note
In Excel 2010, the CONFIDENCE() function was replaced with the CONFIDENCE.NORM() function, and the CONFIDENCE.T() function was added to increase the accuracy of the results. To ensure the backward compatibility of CONFIDENCE.NORM() and CONFIDENCE.T(), the CONFIDENCE() function is still available.
Syntax. CONFIDENCE(alpha,standard_dev,size)
Definition. This function returns a value used to create a confidence interval for the expected value of a random variable. The confidence interval is a range of values. The sample mean x is at the center of this range, and the range is x ± CONFIDENCE.NORM().
For example, if x is the sample mean of delivery times for products ordered through the mail, x ± CONFIDENCE.NORM() is a range of expected values of a random variable. For any expected value of a random variable μ0 in this range, the probability of obtaining a sample mean further from μ0 than x is greater than alpha. For any expected value of a random variable μ0 not in this range, the probability of obtaining a sample mean further from μ0 than x is less than alpha.
Assume you use x, standard_dev, and size to construct a two-tailed test at significance level alpha for the hypothesis that the expected value of a random variable is μ0. Then you will not reject that hypothesis if μ0 is in the confidence interval and will reject that hypothesis if μ0 is not in the confidence interval.
The confidence interval does not allow you to infer that there is a probability of 1 – alpha that your next package will take a delivery time that is in the confidence interval.
Arguments
alpha (required). The probability of error used to calculate the confidence interval. The confidence interval equals 100*(1 – alpha)%, which means that an alpha of 0.05 indicates a 95-percent confidence level.
standard_dev (required). The standard deviation of the population. The argument standard_dev is assumed to be known.
size (required). The size of the sample.
Note
If one of the arguments isn’t a numeric value, the CONFIDENCE.NORM() function returns the #VALUE! error.
If alpha is less than or equal to 0 or greater than or equal to 1, CONFIDENCE.NORM() returns the #NUM! error.
If standard_dev is less than or equal to 0, the function returns the #NUM! error.
If size isn’t an integer, the decimal places are truncated. If size is less than 1, the function returns the #NUM! error.
If alpha equals 0.05, you have to calculate the area under the standard normal distribution that equals (1 – alpha) or 95 percent. This value is ± 1.96. Therefore, the confidence interval is:
Background. In statistics, confidence intervals are a commonly used method to indicate the accuracy of estimated values. The higher the confidence interval, the less accurate the information is. A lower confidence interval is more likely to return the accurate value.
For this reason, the standard deviation for the variance or the confidence interval is usually specified in addition to the calculated mean.
The CONFIDENCE.NORM() function determines the 1 alpha confidence interval for the expected value (mean) of a probability distribution. alpha is the probability of error or the alpha risk. Only values within the interval [0..1] are valid (0 to 100 percent). If alpha is 0.05 (5 percent), the probability that the mean is outside the interval calculated by CONFIDENCE.NORM() is 5 percent. In other words, the probability for the mean of the population to be in the calculated interval is 95 percent. This is called a 95-percent confidence interval.
A sample from a normal distributed population with a known standard deviation and mean is used to calculate the confidence interval. The CONFIDENCE.NORM() function returns half the confidence interval of the arithmetic mean for the sample. This means that the 1 alpha confidence interval is a symmetrical area around the mean of a sample containing the population mean with a probability of 1 alpha.
Example. We will use the example of the software company to explain CONFIDENCE.NORM(). The company sells all its products through its internal website. You are the marketing manager and want to further analyze the numbers of the past four years. You have entered the number of website visits and online orders per month in Excel (see Figure 12-23).
You have also already calculated the sample mean and the standard deviation of the population for both areas. Because you use only one sample, you calculate a 95-percent confidence interval for the population mean (see Figure 12-24).
Because you want a 95-percent confidence level, alpha = 0.05 (5 percent).
Figure 12-25 shows the calculation of the confidence interval for the average of the website visits and the average of the online orders.
For website visits, the CONFIDENCE.NORM() function returns 2,803.57 (half the confidence interval of the arithmetic average for the sample).
To determine the lower and upper limits of the interval, the sample mean plus CONFIDENCE.NORM() as well as the sample mean minus CONFIDENCE.NORM() were calculated.
The result for the lower limit is 8504.54, and the result for the upper limit is 14,111.69.
Statements. You expected alpha to be 0.05 and draw the following conclusions based on the result:
The population mean for the website visits is in the range from 8504.54 through 14,111.69, with a 95-percent confidence interval.
Because the sample size is only 43, the confidence interval is inaccurate. The larger the sample, the more accurate the confidence interval is—and the population mean is also more accurate.