Confidence Interval Function in Excel

Advanced Excel Functions offer excellent and convenient methods for different statistical calculations and analysis and are of great benefit to statisticians. One such feature is the Confidence Interval which statisticians use to express the degree of uncertainty associated with a sample. Confidence Interval is an interval estimate combined with a probability statement.

The Udemy course Confidence Intervals and Sample Sizes Estimations vividly describes the different functions of confidence interval. It explains the concepts of confidence intervals and how to determine sample sizes, how to interpret confidence intervals, how to calculate confidence intervals about the population mean, population proportion, population variance, and population standard deviation.

What is Confidence Interval?

In any survey and user research, confidence intervals are an excellent way of understanding the role of sampling errors in averages and percentages. For any survey, as we almost always sample a fraction of the users from a larger population, there is always uncertainty in our estimates. There will always be sample errors due to sampling. Confidence interval gives an idea about how much the average value is likely to fluctuate.

Significance of Confidence Interval

The confidence interval is a range of values that are centered equally from a known sample mean. The higher your confidence level (percentage) the smaller your interval will be and therefore the more accurate your results will be. Population of samples with more variability or greater standard deviation generates wider confidence intervals. There is also an inverse square root relationship between confidence intervals and sample sizes. Smaller sample sizes generate wider intervals and so for better estimates or to cut your margin of error by half, you need to approximately quadruple your sample size.

How to Construct Confidence Interval?

To construct the confidence interval for a population mean, for a supplied probability and sample size, we need to use the CONFIDENCE function in Excel which uses a Normal Distribution to calculate the confidence value that can be used.

Suppose we randomly selected 100 people, measured their weight, and computed their average weight as 155 Pounds. If we now want to know the average weight of the entire population in that particular city, it is highly unlikely that the average for this bigger population will have the same average as our sample of just 100 people. It is much more likely that our sample mean of 155 Pounds may be approximately equal to an (unknown) population mean and we also need to how accurate is our estimated answer. This uncertainty associated with the interval estimate is called the confidence level. For example, if you describe the interval estimate as a “95% confidence interval”, means that if we used the same sampling method to select different samples and computed an interval estimate for each sample, we would expect the true population parameter to fall within the interval estimates 95% of the time.

The CONFIDENCE (alpha, sigma, n) function returns a value which you can use to construct a confidence interval for a population mean. It is assumed that the sample data follow a standard normal distribution with known standard deviation sigma, and the sample size is n. Alpha is a small probability, such as 0.05, and 0 < alpha < 1.  For a 95% confidence level, alpha is calculated as 1 – 0.95 = 0.05.

If you get an error after performing an Excel Confidence function then it is likely to be one of the following common errors:

1.  #NUM! – Occurs if either alpha is ≤ 0 or ≥ 1 or if the supplied standard deviation is ≤ 0 or if the supplied size argument is < 1
2.  #VALUE! – Occurs if any of the supplied arguments are non-numeric

Let’s assume the sample mean of a sample is x. To calculate the confidence interval for a population mean, the returned CONFIDENCE value must be added to, and subtracted from, the sample mean or x. So in a nutshell,

 Confidence Interval = x ± CONFIDENCE

 The higher is your confidence level (percentage), the smaller will be your interval which will make the results more accurate.

Suppose in our previous example, we observe for a sample of 100 people the average weight is 155 pounds, the population standard deviation is 2.5, and the confidence level of 95%. Here, we can state with 95 percent confidence that the population mean weight will be in the interval 155 +/- 0.489991 where 0.489991 is the value returned by the Excel function CONFIDENCE (0.05, 2.5, 100).

What are Confidence Limits?

Confidence limits are the lower and upper boundaries or values of a confidence interval, or the values that define the range of a confidence interval. The upper and lower bounds of a 95% confidence interval are the 95% confidence limits. If you found that 70 percent of people prefer Diet Coke over Pepsi with a confidence interval of 3 percent and a confidence level of 95 percent, then you can say there is 95 percent probability that the true proportion lies between 67 and 73 percent.

Formats of CONFIDENCE Function

CONFIDENCE functions appear under different syntax in different versions of Excel. For example, Excel 2010 has two functions, CONFIDENCE.NORM () and CONFIDENCE.T (), that help to calculate the width of confidence intervals. You use CONFIDENCE.NORM () when you know the population standard deviation of the measure. On the other hand you use CONFIDENCE.T () when you don’t know the population standard deviation and estimate it from the sample data.

Versions of Excel prior to 2010 only had the CONFIDENCE () function. Its arguments and results are similar to those of the CONFIDENCE.NORM () function.

Explanation of different statistical distributions, margin of errors, mean, median, standard deviation, variance, proportion, and other descriptive statistics are covered extensively under the Udemy courses. Understanding Statistical functions in EXCEL 2010 (Part 1) and Advanced Statistical functions in EXCEL 2010 (Part 2). It may be a good idea to get familiar with these terms and how to effectively use the Advanced Excel Functions before starting with Confidence Interval.