How to find confidence interval in Excel for two samples

How to Calculate a Confidence Interval in ExcelWritten by co-founder Kasper Langmann, Microsoft Office Specialist.The CONFIDENCE function is one of Excels oldest statistical functi

How to find confidence interval in Excel for two samples

How to Calculate a Confidence Interval in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

The CONFIDENCE function is one of Excels oldest statistical functions.

The CONFIDENCE function calculates the confidence value for the confidence interval of a data set. A confidence interval is a defined range of values that might contain the true mean of a data set.

In this tutorial, youll get to know more about the CONFIDENCE function, look under its hood, and figure out how to make it work.

Kasper Langmann, Co-founder of Spreadsheeto

Lets start!

Table of Content

  • Chapter 1:What is the CONFIDENCE function?

Chapter 2: Grab your free exercise file here!

  • Chapter 3:The CONFIDENCE function syntax
  • Chapter 4:How to use the function

Chapter 5:Common Errors

  • Chapter 6: Wrapping things up

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

Ive included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download free exercise file

What is the CONFIDENCE function?

The CONFIDENCE function is an Excel statistical function that returns the confidence value using the normal distribution.

In turn, the confidence value is used to calculate the confidence interval (or CI) of the true mean (or average) of a population.

Its a way to represent the uncertainty of your data in a scientific way. It gives the reader or user a so-called margin of error.

Kasper Langmann, Co-founder of Spreadsheeto

Most of the time, people use 95% as the confidence level. Meaning, out of 100 repeated experiments, the true mean is found in 95 of them.

However, a 95% confidence level is not a standard. You can choose your own confidence level, although, people commonly use 90%  99% to well instill confidence.

After you calculate the confidence value, the confidence interval is presented with the average alongside the confidence value with a plus-minus sign (±) in between.

The CONFIDENCE function syntax

Heres the syntax for this function:

=CONFIDENCE(alpha,standard_dev,size)

It has three (3) required arguments:

  • Alpha (the significance level which is calculated as 1  confidence level; a 95% confidence level has a 0.05 significance level)
  • Standard_dev (the standard deviation of the data set)
  • Size (the population size)
Although the average is not one of the arguments, you have to calculate the average to get the confidence interval. The result from the CONFIDENCE function is added to and subtracted from the average.

Kasper Langmann, Co-founder of Spreadsheeto

If the average is 100 and the confidence value is 10, that means the confidence interval is 100 ± 10 or 90  110.

If you dont have the average or mean of your data set, you can use the Excel AVERAGE function to find it.

Also, you have to calculate the standard deviation which shows how the individual data points are spread out from the mean.

In Excel, there are two functions you can use to calculate the standard deviation: STDEV.P and STDEVPA. Use the first one only  STDEV.P  since it ignores non-numeric data.

New Functions

Excel has released 2 new similar functions, the CONFIDENCE.NORM and CONFIDENCE.T functions with similar arguments.

The CONFIDENCE function is still usable for backward compatibility. However, its recommended to learn the 2 new functions since Excel claimed these functions have improved accuracy.

How to use the function

Using the CONFIDENCE function is easy and straightforward. All you have to do is supply the parameters with their appropriate cell references and/or values.

Lets use the data set shown below:

Here, well be solving for the confidence interval of the time it takes for a certain fast-food company to deliver your order.

Assuming you have the same order for all 10 instances, the delivery takes 55.4 minutes on average with a standard deviation of 8.499.

In addition, the fast-food company committed a 95% confidence value.

Using the CONFIDENCE syntax, well arrive at the following equation:

=CONFIDENCE(0.05,8.499,10) or =CONFIDENCE(E4,E6,E7)

  • Alpha: 0.05 (the significance level which is calculated as 1  confidence level; a 95% confidence level has a 0.05 significance level)
  • Standard_dev: 8.499 (the standard deviation of the data set)
  • Size: 10 (the population size)

From that, we get 5.27 (round up to 2 decimals) as the confidence value.

The confidence interval, which is added to and subtracted from the mean, is 50.13  60.67 or 55.4 ± 5.27.

Simple, right?

Common Errors

As with most functions in Excel, there are common errors users may encounter when using the CONFIDENCE function:

#NUM!

There are 3 possible reasons for this error to occur:

  • alpha is  0 or  1
  • standard_dev is  0
  • size is < 1

#VALUE!

This error happens if the supplied or referenced parameter is a non-numeric value. This is the reason why you should use STDEV.P when finding the standard deviation.

Wrapping things up

The CONFIDENCE function is a great Excel function to construct the confidence interval for a population mean.

The best thing about this function is that learning how to use it is quick and easy. Plus, the syntax is straightforward.

Kasper Langmann, Co-founder of Spreadsheeto

Video liên quan