Excel Confidence Interval (Table of Contents)
Introduction to Confidence Interval in Excel
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 a 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.
Definition of Confidence Interval
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 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.
Syntax;
Syntax for 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
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.
4.9 (2,356 ratings)
View Course
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.
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.
We first need to compute the required parameters for the computation of confidence interval. These are namely Mean, Standard Deviation, Significance Value, Size.
Step 1: In cell E2, use formula as an AVERAGE to calculate the sample mean. Use formula as =AVERAGE(B2:B11).
Step 2: To calculate Sample Standard Deviation, use STDEV.P function in cell E3. Use formula as =STDEV.P(B2:B11) to capture the standard deviation for data.
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.
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.
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.
Step 6: The first parameter value is for alpha, a.k.a Level of Significance (cell E4). Use E4 as the first parameter for this function.
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.
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.
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.
Now, we actually wanted to find out the value for Confidence Interval. For which we need to add and subtract the confidence value from 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 confidence interval for population mean. Use formula as =E2+E6 under E7.
Step 10: Subtract cell E6 from E2 to get the lower bound for confidence interval for population mean. Use formula as =E2-E6 under E8.
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 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 the 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! 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 downloadable excel template. You can also go through our other suggested articles –
