How to Find Upper and Lower Limits of Confidence Interval in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to find the upper and lower limits of a confidence interval in Excel, then this article will help you with 5 different ways. The confidence interval determines the probability of lying a mean value in a range of values. The upper and lower limits of this interval predict the limit of the range where a true mean value may exist. So, let’s start with the main article to know more about the process.


How to Find the Upper and Lower Limits of a Confidence Interval in Excel: 5 Easy Ways

Here, we have the following dataset containing some samples showing their weight distribution. Using this dataset we will determine the upper and lower limits of a confidence level easily.

how to find the upper and lower limits of a confidence interval in excel

For completing this article, we have used the Microsoft Excel 365 version, but you can use any other version at your convenience.


Method-1: Using Excel Add-ins to Find Upper and Lower Limits of a Confidence Interval

Here, we will calculate the limits easily after calculating the confidence interval for the weights quickly using Excel Add-ins.

Using Excel Add-ins to find the upper and lower limits of a confidence interval in excel

Step-01:

First, we have to enable the Add-ins for calculating the confidence interval of the weights.

  • Go to the File.

File tab

  • Select Options.

Then, you will be taken to a new wizard.

  • Go to the Add-ins tab, select Excel Add-ins from the Manage options and finally click Go.

After that, the Add-ins wizard will open up.

  • Check the options Analysis Toolpak, Solver Add-in, and then press OK.

Enabling Analysis Toolpak to find the upper and lower limits of a confidence interval in excel

Step-02:

After enabling the toolpak, we will analyze our data now.

  • Go to the Data tab >> Analyze group >> Data Analysis

Later, the Data Analysis dialog box will pop up.

  • Choose the Descriptive Statistics option and then press OK.

descriptive statistics

In this way, you will get the Descriptive Statistics dialog box.

  • Choose the Input Range as $C$4:$C$14 (the range of the weights) >> Grouped By → Columns >> New Worksheet Ply >> check the options Summary statistics and Confidence Level for Mean (by default 95%).
  • Finally, press OK.

Then, you will get the results in a new worksheet. Among the resultant values, we will utilize the Mean value and Confidence Level for calculating the limits.

  • To have the limit values we have used the following two rows after the created table to determine the Lower Limit and Upper Limit.

limits

  • Type the following formula to get the Lower Limit
=B3-B16

Here, we are subtracting the Mean value from the Confidence Level.

calculating lower limit from confidence interval

  • Apply the following formula in cell B18 to gain the Upper Limit
=B3+B16

Here, we will add up the Mean value with the Confidence Level.

calculating upper limit using confidence interval

Read More: How to Calculate 99 Confidence Interval in Excel


Method-2: Utilizing a Simple Formula

In this section, we will apply a simple formula to calculate the limits manually. For calculation, we have added some rows beside our dataset and inserted 1.96 as the z value (here, 1.96 will work for a 95% confidence level).

utilizing a simple formula to find the upper and lower limits of a confidence interval in excel

Step-01:

First, we will calculate the mean, standard deviation, and sample size using the AVERAGE, STDEV, and COUNT functions.

  • Type the following formula in cell E4.
=AVERAGE(C4:C14)

Here, the AVERAGE function will determine the mean weight of the range C4:C14.

AVERAGE

  • Apply the following formula in cell E5.
=STDEV(C4:C14)

STDEV calculates the standard deviation of the range C4:C14.

STDEV

  • For calculating the sample size use the following formula in cell E6.
=COUNT(C4:C14)

The COUNT function will determine the total number of samples in the range C4:C14.

COUNT

Step-02:

Now, we will calculate the limits by applying our formula easily.

  • Calculate the lower limit using the following formula
=E4-E7*E5/SQRT(E6)

 

Formula Breakdown

  • E7*E5 → becomes
    • 96*14.18514 → 27.803
  • SQRT(E6) → becomes
    • SQRT(11) → The SQRT function will calculate a square root value of 11
      • Output → 3.3166
  • E7*E5/SQRT(E6) → becomes
    • 803/3.3166 → 8.38288
  • E4-E7*E5/SQRT(E6) → becomes
    • 27273-8.38288 → 65.88985

using formula to find the lower limit of a confidence interval in excel

  • Calculate the upper limit by entering the following formula
=E4+E7*E5/SQRT(E6)

 Formula Breakdown

  • E7*E5 → becomes
    • 96*14.18514 → 27.803
  • SQRT(E6) → becomes
    • SQRT(11) → The SQRT function will calculate a square root value of 11
      • Output → 3.3166
  • E7*E5/SQRT(E6) → becomes
    • 803/3.3166 → 8.38288
  • E4+E7*E5/SQRT(E6) → becomes
    • 27273+8.38288 → 82.65561

upper limit

Read More: How to Calculate 95 Percent Confidence Interval in Excel


Method-3: Applying CONFIDENCE Function to Find Upper and Lower Limits of a Confidence Interval

Here, we will apply the CONFIDENCE function to calculate the confidence interval at 95% which means the alpha value would be 5% or 0.05.

Applying CONFIDENCE Function to Find Upper and Lower Limits of a Confidence Interval

Steps:

  • Follow Step-01 of Method-2 to calculate the Mean, Standard Deviation, and Sample Size of the weights.

  • Apply the following formula in cell E8.
=CONFIDENCE(E7,E5,E6)

Here, E7 is the significant value or alpha, E5 is the standard deviation, and E6 is the sample size. CONFIDENCE will return the confidence interval of this range.

  • To gain the lower limit subtract the mean value from the confidence interval.
=E4-E8

Applying CONFIDENCE Function to find Lower Limit of a Confidence Interval

  • For the upper limit add the mean value with the confidence interval.
=E4+E8

Read More: How to Calculate 90 Percent Confidence Interval in Excel


Method-4: Implementing NORMSDIST and CONFIDENCE.NORM Functions

Here, we will use the NORMSDIST function to calculate the normal distribution of the z value (for this function the z value will be 1.645 for a 95% confidence level) and then the CONFIDENCE.NORM to calculate the confidence interval.

Implementing NORMSDIST and CONFIDENCE.NORM Functions to find the upper and lower limits of a confidence interval in excel

Steps:

  • Follow Step-01 of Method-2 to calculate the Mean, Standard Deviation, and Sample Size of the weights.

  • To calculate the confidence level percentage apply the NORMSDIST function in cell E8.
=NORMSDIST(E7)

Here, E7 is the z value.

  • Type the following formula in cell E9.
=CONFIDENCE.NORM(1-E8,E5,E6)

Here, 1-E8 will return the alpha or significant value which will be 0.05, E5 is the standard deviation, and E6 is the sample size. CONFIDENCE.NORM will return the confidence interval of this range.

calculating confidence interval

  • To gain the lower limit subtract the mean value from the confidence interval.
=E4-E8

  • For the upper limit add the mean value with the confidence interval.
=E4+E8

upper limit

Read More: How to Calculate Confidence Interval in Excel


Method-5: Utilizing NORM.S.INV and SQRT Functions to Find Upper and Lower Limits of a Confidence Interval

For this section, we will use the NORM.S.INV function to calculate the limits of a confidence interval.

Utilizing NORM.S.INV and SQRT Functions to Find Upper and Lower Limits of a Confidence Interval

Steps:

  • Follow Step-01 of Method-2 to calculate the Mean, Standard Deviation, and Sample Size of the weights.

  • For calculating the lower limit apply the following formula in cell E7.
=$E$4-NORM.S.INV(0.975)*($E$5/SQRT($E$6))

 Formula Breakdown

  • S.INV(0.975) → it will return the value of the z which will be used for calculating the confidence interval (for the 95% level we have to use 0.975 here)
    • Output → 1.95996
  • SQRT(E6) → becomes
    • SQRT(11) → The SQRT function will calculate a square root value of 11
      • Output → 3.3166
  • $E$5/SQRT(E6) → becomes
    • 185/3.3166 → 4.2769
  • S.INV(0.975)*($E$5/SQRT($E$6)) → becomes
    • 95996/4.2769 → 8.3827
  • $E$4-NORM.S.INV(0.975)*($E$5/SQRT($E$6)) → becomes
    • 27273- 8.3827 → 65.88985

Utilizing Functions to Find Lower Limit of a Confidence Interval

  • To have the upper limit apply the following formula in cell E8.
=$E$4+NORM.S.INV(0.975)*($E$5/SQRT($E$6))

 Formula Breakdown

  • S.INV(0.975) → it will return the value of the z which will be used for calculating the confidence interval (for the 95% level we have to use 0.975 here)
    • Output → 1.95996
  • SQRT(E6) → becomes
    • SQRT(11) → The SQRT function will calculate a square root value of 11
      • Output → 3.3166
  • $E$5/SQRT(E6) → becomes
    • 185/3.3166 → 4.2769
  • S.INV(0.975)*($E$5/SQRT($E$6)) → becomes
    • 95996/4.2769 → 8.3827
  • $E$4-NORM.S.INV(0.975)*($E$5/SQRT($E$6)) → becomes
    • 27273+ 8.3827 → 82.65545


Practice Section

For doing practice, we have added a Practice portion on each sheet on the right portion.

Practice


Download Practice Workbook


Conclusion

In this article, we tried to show the ways to find the upper and lower limits of a confidence interval in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles


<< Go Back to Confidence Interval Excel | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo