Microsoft Excel is a powerful tool for working with data and figuring out statistics. Statisticians, scientists, and people in many other fields need to know how to use Excel to make a confidence interval. In statistics, you can use Excel to find the upper and lower bounds. You can do this with a built-in function or by hand. The steps are easy to follow.
Statistics Upper and Lower Bounds
In statistics, the upper and lower bounds are the points at which a 95 percent confidence interval ends. If you know the average height of a population, for example, you can use confidence intervals to figure out how much variation there is in the population. So, for example, the average height of American men might be 70 inches (5 feet 10 inches), but the 95 percent confidence interval could range from 64 to 76. This shows that even though the average height is 70 inches, 95 percent of people are between these two heights. We call the lower value the lower bound and the higher value the upper bound.
The average and the standard deviation
You need the mean of the data set, the number of data points, and the standard deviation to use Excel as an upper and lower bounds calculator. Excel makes it easy to find these numbers because it has functions built in that can figure them out. Choose an empty cell and enter “=AVERAGE(” before selecting all the cells that contain your data and then closing the parentheses. So, if you have 100 data points between cells C2 and C101, the field might say “=AVERAGE(C2:C101)”. The mean of the data is what this function gives back.
Choose another empty cell and enter “=STDEV(” before selecting all the cells containing the data again. This gives you the data’s standard deviation. To get the value for the data from C2 to C101, you would type “=STDEV(C2:C101).” If you need to, you can also use the “Count” function to find out how many points there are in total. In the example, you do this by entering “=COUNT(C2:C101)” into an empty cell, and it returns the value of 100.
Excel’s “Confidence Function”
The “Confidence” function in Excel is the best way to find a confidence interval. Type “=CONFIDENCE(” into Excel to bring up the function. This is written as “=CONFIDENCE(alpha, standard deviation, sample size),” where “alpha” is the level of significance you want to know about. Most of the time, the level of confidence is 95%, so the alpha is 0.05. For a confidence level of 99 percent, the alpha is 0.01. The “STDEV” function gives the value of the standard deviation, and the “Count” function gives the size of the sample.
Put the correct values into the function. Imagine that the standard deviation is calculated in cell D2 and the count is calculated in cell D3. In this case, the correct value for the function would be returned by “=CONFIDENCE(0.05, D2, D3).”
Add the value returned by the Confidence function to your mean, which is what the Average function gives you, to find the upper limit. Take the mean and subtract the result of the Confidence function. This will give you the lower limit. The confidence interval is the range between these two points.
Calculating the Bounds by Hand
You can also do this calculation by hand. First, divide the value you got from the STDEV function for the standard deviation by the square root of the number of samples you got from the Count function. If the standard deviation is in cell D2 and the count is in D3, enter “=D2/SQRT(D3)” to find the value. In the example, there are 100 people in the sample, so the square root is just 10. The same thing will happen if you write “=D2/10.”
Add 1.96 times this number to your mean value to find the upper bound. So, if the mean is in cell D1 and the last result is in cell D4, type “=D1+(1.96D4)” into a blank cell to get the result. To find the lower limit, pick a blank cell and type “=D1-(1.96D4)” in it. Keep in mind that this gives you the 95% confidence interval. Use a different number instead of 1.96 if you want the 99 percent confidence interval or another value.