How to Find the Upper and Lower Bounds in Excel

Microsoft Excel is a powerful tool when it comes to handling data and performing statistical calculations. Creating a confidence interval in Excel is a key skill for statisticians, scientists and people in a range of professions. Excel can be used to find the upper and lower bounds in statistics, and you can do this either using a built-in function or manually. The process is straightforward.

The upper and lower bounds in statistics generally refer to the endpoints of a 95 percent confidence interval. If you have a mean value for the population – for instance, an average height – you use the confidence intervals to give a sense of how much variation there is across the population. So for example, the mean height for American men might be 70 inches (5 foot 10 inches), but the 95 percent confidence interval could stretch from 64 to 76. This tells you that while the mean is 70 inches, 95 percent of the population fall between these two heights. The lower value is called the lower bound, and the higher value is called the upper bound.

The main things you need when using Excel as an upper and lower bounds calculator are the mean of the data set, the number of data points and the standard deviation. These are easy to find using Excel because it has built-in functions that calculate them. Choose an empty cell and enter "=AVERAGE(" before selecting all the cells that contain your data and then closing the parentheses. So the field might read "=AVERAGE(C2:C101)" if you have 100 data points between cell C2 and C101. This function returns the mean of the data.

Choose another empty cell and enter "=STDEV(" before selecting all the cells containing the data again. This returns the standard deviation of your data. In the example of data running from C2 to C101, you enter "=STDEV(C2:C101)" to return the value. You can also use the "Count" function to find the total number of data points, if necessary. In the example, you do this by entering "=COUNT(C2:C101)" into an empty cell, and it returns the value of 100.

The simplest tool for finding a confidence interval in Excel is the "Confidence" function. Type "=CONFIDENCE(" into Excel to bring up the function. The format for this is: "=CONFIDENCE(alpha, standard deviation, sample size)," where "alpha" is the significance level you're interested in. In most cases, the confidence level is 95 percent, so the alpha is 0.05. For a 99 percent confidence interval, the alpha is 0.01. The standard deviation is the value given by the "STDEV" function, and the sample size is given by the "Count" function.

Enter the values into the function as required. Imagine that in the example, the standard deviation is calculated in cell D2 and the count is calculated in cell D3. In this case, "=CONFIDENCE(0.05, D2, D3)" would return the correct value for the function.

Find the upper limit by adding the value returned by the Confidence function to your mean, which is the output of the Average function. Find the lower limit by subtracting the output of the Confidence function from the mean. The range between these two limits is the confidence interval.

You can perform this same calculation manually. First, divide the value of the standard deviation you calculated using the STDEV function by the square root of the sample size returned by 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, the sample size is 100, so the square root is simply 10. You can also write "=D2/10" to get the same result.

Find the upper bound by adding 1.96 multiplied by this result to your mean value. So if the mean is in cell D1 and this last result is in D4, enter "=D1+(1.96D4)" into a blank cell to get the result. To find the lower bound, choose another empty cell and enter "=D1-(1.96D4)." Note that this returns the 95 percent confidence interval. If you want the 99 percent confidence interval or another value, you use another number in place of 1.96.