How to Calculate a Confidence Interval in Excel (2022)

The CONFIDENCE function in Excel is as old as Excel itself and just as useful. And it is one of the most commonly used statistical analysis tools.

To put simply, The CONFIDENCE function helps calculate confidence interval with the help of a confidence value and standard deviation. Confusing, right?

Don’t worry. We will learn everything about the CONFIDENCE function in the easiest way possible.

So without further ado, let’s get right into it. 😀

Also, if you want to tag along with the guide, feel free to download our sample workbook here.

Table of Contents

What does the CONFIDENCE function do?

The CONFIDENCE function uses the mean of a data range to return the confidence value. It is one of the most commonly used functions by a financial analyst.

We calculate the confidence value using the function. And from the confidence value, we calculate the confidence interval. It shows the uncertainty of your data analysis scientifically. And gives you a margin for human error.

Typically, people use a confidence level of 95% in most of their calculations. This means that out of 100 calculations – there is a probability value for 95 calculations to be correct. 😮

Once you’re done with the confidence value, you get the confidence interval.

The confidence interval represents the confidence value and the mean value. It comes with a plus-minus sign indicating an approximate value.

The CONFIDENCE function returns an accurate confidence interval. Regardless of how complex data is given. We’ve seen what the CONFIDENCE function is. Now, let’s see its syntax and how to calculate confidence intervals.

Syntax of the CONFIDENCE function

The CONFIDENCE function syntax is 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 – represented in decimals. (for a 90% confidence limit, the significance level is 0.010.)
• standard_dev: Standard deviation for the required data set
• size: Population size

You can find the mean manually or use the Microsoft Excel AVERAGE function – it’s pretty simple to use.

You also need to find the standard deviation of the data set to add in the confidence interval formula. The standard deviation shows the mean is different from the individual data points.

Only recently, Excel launched a new version of the function, and you can use either of STDEVPA. and STEV.P. 🤔

Pro Tip!

It is better to use STEV.P for finding standard deviation as it returns numeric values only. Even if your data has a non-numeric value, it will not be shown in the result.

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.

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?

The process is pretty simple and straightforward. All you need to do is enter the arguments, get the confidence value statistic, and it’s done. Simple, no? 😀

Let’s see it using the data set below.

We have to find the confidence interval of the total staff of a small jewelry store over the years.

We have the values. Now, let’s find the average value of staff hired over the years.

1. Type in the AVERAGE function beneath the values and select the cell references.

2. Press enter.

And there’s the average. Let’s move toward the standard deviation now.

1. Type in the formula.
2. Select the values.

3. Press enter.

We have the standard deviation value too.

Let’s assume that our confidence value is 95%, so our Alpha value will be 0.05. Add that to the Excel worksheet.

Add in the other values for calculating the confidence interval.

Let’s now type in the formula.

=CONFIDENCE(B2, B4, B5)

Hit Enter.

And we got the confidence value. Now, let’s find the confidence interval.

All we need to do is write the average and confidence values together after rounding them off. The confidence interval of our data set is:

And tada! We did it. 😃

Typical CONFIDENCE function mistakes

Just like any other Excel function, you may encounter some common CONFIDENCE errors while using this function. There are two typical errors that Excel shows. These are:

#NUM!

This error may show up for one of three reasons provided you’ve entered the correct data.

• The alpha value is less than and equal to zero or greater than and equal to one.
• The standard deviation is less than and equal to zero.
• The sample size is less than one.

Fixing these small problems should remove the error.

#VALUE!

Excel shows this error because of non-numeric values in the given data while calculating 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. We also saw how to calculate confidence intervals in Excel and the common errors people encounter.

The CONFIDENCE function offers a more simplified process for concise statistical computation. 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 VLOOKUP, IF, and SUMIF functions.

Enroll in my 30-minute free email course to learn these functions (and more!)

Other resources

The process of finding confidence intervals is very straightforward. And you can do it in minutes once you get a hold of it. If you enjoyed reading this article, we bet you’d want to read more such articles.

Some of these are the AVERAGE, STDEV, and FORECAST CONFIDENCE functions.