Confidence Interval In Excel | How to Calculate Confidence Function?

Excel Confidence Interval

Statistics isn’t the easiest thing to digest, especially at the beginning of the learning most of the people find it tough to get inside and get used to it. Finding the confidence interval in statistics is the complex calculation on paper but using excel we can construct confidence interval very quick and fast. But in the end, it boils down how well you know about excel and what are the things you need to calculate the confidence interval in excel.

In this article, we will take you through the process involved in finding a confidence interval in excel.

What is Confidence Interval?

Confidence Interval is the range of population values that our true values lie in. This takes the CONFIDENCE value which is centered around the population mean of the range of values.

So, by finding CONFIDENCE value we can easily construct the confidence interval value around the average mean of the sample values.

What Does Confidence Function Do in Excel?

CONFIDENCE is a statistical function in excel that uses the mean value of the sample data, a standard deviation of the sample and the number of samples to calculate the confidence value to construct confidence interval value.

Syntax

Below is the syntax of the CONFIDENCE function in excel.

Alpha: This is the significance which is at 1 confidence level, if 90% is the confidence level then significance level 0.10.
Standard Deviation: This is the SD of the data range.
Size: The number of observations in the data set.

To compare confidence interval value we need to further calculate the mean of the data set. We will see some of the examples to understand practically.

How to Calculate & Find Confidence Interval Function in Excel?

Below is an example of calculating confidence interval in excel.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

You can download this Confidence Interval Excel Template here – Confidence Interval Excel Template

One of the food delivery company conducted a survey on delivering the food to the customer on numerous occasions and they have recorded the time taken each time to deliver the food to the customer.

Solution

Below is the sample data of the same.

From the above data, we need to solve the confidence interval time to deliver the food quickly. Follow the below steps to calculate the confidence value.

Step 1: Copy the above data to excel worksheet first.

Step 2: From the above data, we need to calculate certain values that are essential for the CONFIDENCE function in excel. So, the first thing we need to calculate is the MEAN value. So to calculate the mean value using the AVERAGE function.

So, the average time is taken to deliver the food on 10 occasions in 52 Minutes.

Step 3: Calculate Standard Deviation of the data set by using the STDEV.P function.

Step 4: Now we need to note down what is the confidence level of the food delivery company to commit themselves to deliver the food quickly. Let’s says they are committing at a 95% confidence level then we need to calculate the significance value.

Significance value will be 1 – Confidence Value = 5% i.e. 0.05

Step 5: The final part we need to note down is what is the number of occasions the experiment has been conducted, in this case, 10 times.

Using these values we will calculate the CONFIDENCE value.

Step 6: Open CONFIDENCE function in E6 cell.

Step 7: The first argument of this function is Alpha i.e. what is the significance value. So our significance value is 0.05 which is there in cell E4.

Step 8: Next up is the “Standard Deviation” of the sample data. We have already calculated this SD in cell E3, so give the cell reference.

Step 9: Final argument of the CONFIDENCE function is “Size” i.e. number of experiments conducted, so give cell reference as E5 cell.

Step 10: Ok, that’s all. Close the bracket and hit enter key to get the confidence value.

So, the confidence value of the data series is 8.30, using this we can construct a confidence interval value.

Confidence Interval value in excel is arrived by adding and subtracting the confidence value from the MEAN of the data set.

So, Confidence Interval (CI) = MEAN ± Confidence Value.

CI = 52 ± 8.30
CI = 52 + 8.30 or 52 – 8.30
CI = 44.10 to 60.70.

Things to Remember Here

Confidence Interval in Excel is the plus or minus value of the average of the data set and the confidence value.
The confidence function in excel accepts only numerical values.
In recent versions CONFIDENCE function in excel is upgraded to CONFIDENCE.NORM & CONFIDENCE.T functions.