EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel Confidence Interval in Excel
Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course

Confidence Interval in Excel

By Madhuri ThakurMadhuri Thakur

Confidence Interval in Excel

Excel Confidence Interval (Table of Contents)

  • Definition of Confidence Interval
  • How to Compute Confidence Interval?

Introduction to Confidence Intervals in Excel

Confidence Interval is nothing but a range of values within which a chance of lying the population parameter is really high. Within Excel, we have a CONFIDENCE function that allows us to find out the confidence interval for a population parameter based on the sample data values. This function is categorized within Statistical Functions under Excel and uses the Normal Distribution or approximation towards Normal Distribution while deciding the Confidence Interval for a population parameter.

Confidence intervals are the integral parts of Statistical Calculations for an analyst and have a major impact on the decisions that he/she takes based on the data. Confidence Interval is an interval (range of values) with high chances of true population parameters lying within it. On paper, it seems to be one of the hardest calculations to crack. However, with the help of Excel, you can calculate one with minimal efforts as well as a fuss. In this article, we will take a deep dig and see the process of finding out a confidence interval under Excel with the help of some Excel Functions.

Syntax;

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

The syntax for the CONFIDENCE function that allows us to find out confidence interval under Excel is as shown below:

=CONFIDENCE.NORM(alpha, standard_dev, size) (If you are using Excel version above 2007) OR

Confidance norm

Well, both these functions work exactly the same and have no difference while calculating the confidence interval. These two functions use Normal Distribution or approximation towards Normal Distribution while calculating the confidence interval.

Arguments:

  • alpha – This is a required argument which is also called a level of significance and computed with the formula as 1 Confidence value. Thus, if you want a 95% confidence value, the level of significance would be 1 – 0.95 = 0.05 or 5%.
  • standard_dev – This is a required argument that specifies the standard deviation of the sample data given.
  • Size – This is a required argument that specifies the number of sample points present within the sample data.

In Statistics, in order to find out the confidence interval, we need to add and subtract the confidence value from the sample mean.

Confidence Interval = Sample Mean ± Confidence Value.

Let’s see how we can find out the confidence interval for a population means based on the sample data provided.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,813 ratings)

How to Compute Confidence Interval?

Suppose we have data of marks obtained by 10 students in a class of standard 10th, as shown in the screenshot below. All we need to compute is a Confidence Interval for the men’s population marks based on the sample data of these 10 students. Let’s see how this can be done.

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

Confidence Interval in Excel 1-1

We first need to compute the required parameters for the computation of confidence interval. These are namely Mean, Standard Deviation, Significance Value, Size.

Confidence Interval in Excel 1-2

Step 1: In cell E2, use the formula as an AVERAGE to calculate the sample mean. Use formula as =AVERAGE(B2:B11).

Confidence Interval in Excel 1-3

Confidence Interval in Excel 1-4

Step 2: To calculate Sample Standard Deviation, use the STDEV.P function in cell E3. Use formula as =STDEV.P(B2:B11) to capture the standard deviation for data.

Confidence Interval in Excel 1-5

Step 3: We want a 95% confidence interval for the population mean, for which we will set the level of significance as 1-0.95 = 0.05. Set the value in cell E4.

Confidence Interval in Excel 1-6

Step 4: For capturing the sample size, we can use the COUNT function to count the size of column B. use formula as =COUNT(B2:B11) under cell E5.

Confidence Interval in Excel 1-7

Step 5: In cell E6, we will find out the Confidence Value using CONFIDENCE.NORM function. Start initializing the function by typing “=CONFIDENCE.NORM(“ in cell E6.

Confidence Interval in Excel 1-8

Step 6: The first parameter value is for an alpha, a.k.a Level of Significance (cell E4). Use E4 as the first parameter for this function.

Confidence Interval in Excel 1-9

Step 7: Use Sample Standard Deviation as a second parameter under the formula. The value is stored in cell E3. Add it as a second argument under the formula.

Standard Deviation

Step 8: Sample Size is the last parameter for the function. It is stored under cell E5. Use this cell as an argument in the formula and close the bracket to complete it.

last parameter for the function

Press Enter key, and you can see the confidence value for this sample data of marks for ten students as shown below under cell E6.

confidence value

Now, we actually wanted to find out the value for Confidence Interval. For which we need to add and subtract the confidence value from the sample mean, which is captured under cell E2. Those two values will be called as Confidence Lower Bound and Confidence Upper Bound.

Step 9: Add cell E6 in E2 to get the upper bound for the confidence interval for the population mean. Use formula as =E2+E6 under E7.

upper bound

Step 10: Subtract cell E6 from E2 to get the lower bound for the confidence interval for the population mean. Use formula as =E2-E6 under E8.

population mean

Thus, the 95% Confidence Interval for Average Marks obtained by the population of students based on these ten students is (60.3133, 81.8867). this means that 95 times out of 100, the average mean marks scored will fall between 60.3133 and 81.8867 for the population of students from which these ten students are sampled.

Please note that we can also use the CONFIDENCE function to capture the 95% confidence value for the population mean. As said earlier, these two functions are identical and should give the same value for confidence value (You can give it a try for this and let me know your outputs.

This is how we can calculate a confidence interval under Excel using some basic arithmetic and CONFIDENCE.NORM/CONFIDENCE function. This article ends here; let’s wrap things up with some points to be remembered:

Things to Remember

  • CONFIDENCE function is replaced by CONFIDENCE.NORM under Excel’s 2010 and later versions. The former is still available in Excel for compatible issues.
  • We get #NUM! error while calculation confidence value using CONFIDENCE.NORM/CONFIDENCE function if any of the three parameters (alpha, standard-dev, size) has a value less than or equals to zero.
  • #VALUE! an error occurs when any of the three-argument has provided with a non-numeric value.

Recommended Articles

This is a guide to Confidence Interval in Excel. Here we discuss How to compute Confidence Interval in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Worksheets in Excel
  2. Excel AVERAGE Function
  3. COUNTIFS in Excel
  4. MID Function in Excel
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Confidence Interval Excel Template

EDUCBA

Download Confidence Interval Excel Template

EDUCBA

डाउनलोड Confidence Interval Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more