How to Calculate a Confidence Interval in Excel (2024)
The CONFIDENCE function of Excel is as old as Excel itself and is just as useful.
You might not have used it before. But to your surprise, it is one of the most commonly used statistical analysis tools of Excel!
The CONFIDENCE function calculates the confidence interval for the mean of a population.
Confused? No worries. Dive straight into the guide below to learn all about the CONFIDENCE function of Excel🚀
Also, our free sample workbook is available here for you to download and tag along with the guide.
Table of Contents
What does the CONFIDENCE function do?
To learn what the CONFIDENCE function does, we must first understand what is meant by confidence here
Speaking in terms of statistics, confidence means the probability that a population parameter will fall between two values.
Pro Tip!
For example, you want to know the weight of a pack of biscuits. So you gathered a sample of 10 packs of biscuits and weighed them all🍪
Each of them had a slightly different weight and the mean (average) of these 10 weights turned out to be 50 grams📏
However, not each pack weighs exactly 50 grams. There might be a deviation of up to +5 grams or -5 grams in the weight of each pack. Let’s say, the 95% confidence interval for this turns out as 1.2 grams. What does this mean?
This means that there is a 95% chance that the mean weight of the whole population (all biscuit packs) would fall somewhere between 48.8 grams to 51.2 grams. (50+1.2 = 51.2 and 50-1.2=48.8).
The CONFIDENCE function calculates the confidence interval for the mean of the population.
Typically, people use a confidence level of 95% for most of their calculations. This means that there is a 95% probability the population mean would fall within the confidence interval range🎯
95 is not a standard significance value for confidence. Often users choose different confidence values like 91, 96, 98, etc.
The CONFIDENCE function returns an accurate confidence interval. Regardless of how complex the data might be.
Syntax of the CONFIDENCE function
The syntax of the CONFIDENCE function syntax reads as follows:
=CONFIDENCE(alpha, standard_dev, size)
Let’s discuss each of its arguments below.
- alpha: A significance level that lies anywhere between 0 & 1. 10% significance level means 90% confidence.
- standard_dev: Standard deviation of the population.
- size: Sample size
Simple enough!
But how do we get the standard deviation to input in the formula above?
The standard deviation shows how different the actual data might be from the mean of a sample size. Excel offers many formulas to find the standard deviation in Excel.
Like the STDEVPA. and STEV.P functions of Excel 🔍
Pro Tip!
It is better to use the STEV.P function for finding standard deviation.
It returns numeric values only. Even if your data has a non-numeric value, it will not be shown in the result.
And oh! We’d also need the mean of the sample size to reach the probable range of values🎭
Mean can be found using the Microsoft Excel AVERAGE function.
How to use the Excel CONFIDENCE function?
Enough with all the talk. Let’s come to the point – how do you use the CONFIDENCE Function?
Calculating confidence intervals in Excel is pretty simple. All you need to do is enter the relevant data points, get the confidence value statistic, and that’s it. Simple😜
Let’s say we want to find the mean weight of the employees of a Company.
For that, we have drawn a sample of 6 employees, and here are their weight statistics.
First of all, we need to find the mean of this sample of six employees. Only then can we add/deduct the confidence interval from it to reach the mean weight for all the employees.
We have the weights of our sample. Now, let’s find the average (mean) weights of all these employees.
- Type in the AVERAGE function as follows:
= AVERAGE (B2:B7)
- Press enter.
And there’s the average.
Let’s move toward calculating the standard deviation now. We need the standard deviation of these weights as it makes an argument of the CONFIDENCE function.
- Write the STDEV formula as follows:
= STDEV (B2:B7)
- Press enter.
We have the standard deviation value too.
Huff! By now, we have all our arguments for the CONFIDENCE function ready except for the alpha value.
Let’s take a significance level of 5% which implies a confidence level of 95%.
And so, our Alpha value will be 0.05.
All set!
- Write the CONFIDENCE formula as follows:
=CONFIDENCE(B2, B4, B5)
The CONFIDENCE function has three arguments:
- The first argument is the Alpha Value. This depends upon the significance level taken by the users. We have set it to 95%. So our alpha value is 5%.
- The second argument is the Standard Deviation. We calculated the standard deviation for our sample and it turned out to be 15.83.
- The third argument is the sample size. Not to forget, we chose a sample of six employees. So our sample size is 6.
Kasper Langmann, Microsoft Office Specialist- Hit Enter.
And there we have our confidence interval!
But what does this confidence interval mean? The confidence interval represents the following range:
59.83 Kgs – 85.17 Kgs
Pro Tip!
How did we calculate this? Remember the mean weight for the sample of our six employees? We calculated it using the AVERAGE function and it turned out 72.5💭
Simply add and deduct the confidence interval of 12.67 from this mean value.
- 72.5 – 12.67 = 59.83
- 72.5 + 12.67 = 85.17
This tells that there is a 95% chance that the mean weight of all the employees of the Company will fall somewhere between 59.83 Kgs to 85.17 Kgs 🧐
Typical CONFIDENCE Function Mistakes
Just like any other Excel function, you may encounter some common errors while using the CONFIDENCE function in Excel.
There are two typical errors that the CONFIDENCE function commonly poses🚩
#NUM!
This error may show up for one of the following three reasons:
- The alpha value is less than or equal to zero or greater than or equal to one.
- The standard deviation is less than or equal to zero.
- The sample size is less than one.
Fixing these small problems should remove the error.
Pro Tip!
Do you know? Excel has also released two new versions of the CONFIDENCE functions. These are the CONFIDENCE.T & CONFIDENCE.NORM 👀
They have the same arguments as the CONFIDENCE function but have improved accuracy. You can still use the CONFIDENCE function for backward compatibility.
#VALUE!
Excel shows this error because of non-numeric values in the data for standard deviation. This is why it is recommended to use the STDEV.P function to avoid these errors.
That’s it – Now what?
In this article, we learned what the CONFIDENCE function is about. We also saw how to calculate confidence intervals in Excel. And the common errors Excel users encounter using this function.
The CONFIDENCE function offers a more simplified process for complex statistical calculations. But it doesn’t end there.
Excel has a wide range of new functions for statistical computations. And knowing them can be really helpful.
If you’re new to this giant spreadsheet software, we recommend you start by learning the basics like the VLOOKUP, IF, and SUMIF functions.
Enroll in my 30-minute free email course to learn these functions (and more!)