How to find confidence interval in excel

  • Post author:
  • Post category:Uncategorized

We can apply the Descriptive Statistics tool to calculate confidence interval easily. Confidence interval specifies a range of values where there is a probability that the true value of a parameter lies. In this tutorial, we will learn the easy way of calculating confidence interval.

Figure 1 – Result of 95% confidence interval

How to Find 95% Confidence Interval

  • We will use figure 2 to illustrate how we can find the 95% confidence interval of a set of values.

Figure 2 – How to calculate confidence interval

  • We will click the Data tab, and then, the Data Analysis tool

Figure 3 – Click on Data Analysis

  • If we do not have the Data Analysis tool, we should do the following:
  • We will click File, Options, Add-Ins.
  • We will click Go
  • We will check the Analysis Toolpak box and the Analysis Toolpak VBA box

Figure 4 – Inserting the Data Analysis tool

  • Once we have clicked Data Analysis, we will see the Data Analysis dialog box. We will select Descriptive statistics and click OK
  • On the descriptive statistics dialog box, we will click Input range and select the range of values from Cell B4 to Cell B11.  We will insert an Output range or check the New worksheet box for the result to be displayed in another worksheet. Our confidence interval will be set at 95% and we will check the Summary statistics box

Figure 5 – How to calculate 95% confidence interval

  • We will click OK

Figure 6 – Result of Descriptive statistics

  • Notice that in Row 18, the result is Confidence level and not Confidence interval
  • To get the Confidence Interval, we need the Mean and the Confidence Level

Figure 7 – How to find the 95% confidence interval

  • The Lower Limit is the mean minus the confidence level and the Higher limit is the mean plus the confidence level
  • In Cell G5, we will paste the formula: =G3-G4
  • In Cell G6, we will paste the formula: =G3+G4

Figure 8 – Result of 95% confidence interval

  • The confidence interval of the values is 19.1434 ± 23.6066. To get the 90% Confidence Interval or any confidence level we want, we will simply adjust the Confidence level in Figure 5.