This lesson reviews the calculations required for constructing confidence intervals for a population mean and introduces the Excel functions CONFIDENCE.NORM and CONFIDENCE.T, which are specially tailored for such calculations. We also work out an application involving the confidence interval for a population mean. In our study of the confidence interval, we have so far had a conceptual understanding of the confidence interval, looked at a few examples, and also solved a stylized problem in which we constructed the confidence interval for the unknown population mean. We used this example to introduce the calculation for the confidence interval for a population mean. This is as shown. Alpha is the probability outside the confidence interval. For example, alpha would be equal to 0.05, for 0.95 or 95% confidence interval. Mu is the unknown population mean for which we wish to create a confidence interval. x-bar is the sample mean obtained from a sample of data. And the dotted underlined expression is the margin of error, which itself is constructed of the absolute value of z alpha/2. Which is the value of the z-statistic that cuts off alpha/2 probability in a standard normal distribution, the Excel function for which is NORM.INV, alpha/2, 0, 1. This is then multiplied by sigma, the population standard deviation, and divided by the square root of sample size. There also happens to be an Excel function that calculates this margin of error. The function is CONFIDENCE.NORM. And it takes in three inputs, alpha, which is the probability outside the confidence interval, the population standard deviation sigma, and the sample size n. So there are two ways of calculating the margin of error, either using this CONFIDENCE.NORM function, or doing the calculation of z alpha/2 multiplied by sigma by square root of sample size. So x-bar, or sample mean minus the margin of error gives us the lower limit of the confidence interval. And x-bar, or sample mean plus margin of error gives us the upper limit. One thing to be noted here is that the calculation for confidence interval for the population mean uses the z-statistic. This is because we have the population standard deviation and the calculation, implying that we need to know its value to calculate the confidence interval. In situations where we do not know the population standard deviation, we replace it with the sample standard deviation. And the z-statistic, as was discussed in an earlier lesson, gets replaced by the t-statistic. The calculation for the confidence interval then becomes as shown. Where lower case s is the sample standard deviation and t alpha/2 is the value of the t-statistic that cuts off alpha/2 probability in the t-distribution with n minus 1 degrees of freedom. The Excel function for t alpha/2 being T.INV(alpha/2, n- 1). The margin of error now is the absolute value of t alpha/2 times sample standard deviation, by square root of sample size. And there is also an Excel function to directly calculate this margin of error, which is CONFIDENCE.T. And this function takes three imports, alpha, the probability outside confidence interval, s, the sample standard deviation, and n, the sample size. Note that this function is analogous to the CONFIDENCE.NORM function used to calculate the margin of error when using the calculation with the z-statistic. Let us work on an application. A person wishes to explore the size of single family houses that are typically available for purchase, in a particular neighborhood of Houston in Texas. She manages to get hold of a list containing house sizes of a sample of 100 houses that were sold in the past two years in this particular neighborhood. The data is provided in the Excel data file Home_Sizes.xlsx. And given this data, she wishes to asses the average size of houses typically available for purchase in this neighborhood. Let us take a look at this data. This file, Home_Sizes.xlsx, contains house sizes for a random selection of 100 houses sold in the past two years in a particular neighborhood of Houston in Texas. The units of measurement of home sizes are in square feet. Note that she does not have an exhaustive list of houses and their sizes for this neighborhood. In other words, she does not have the population data on house sizes. All she has is a sample of 100 house sizes from that population data. And based on this sample, she wishes to assess the average size of houses. Here, she could construct a confidence interval for the average house size. And we will shortly construct this confidence interval using a confidence level of 95%, which is typically used in industry applications. A confidence level of 95% implies our value of alpha, which is the probability outside the confidence interval, is 0.05 or 5%. In this application, there is no information on the variability of house sizes across the population of houses in this neighborhood. Or in other words, we do not know the population standard deviation for house sizes in this neighborhood. However, we can calculate the variability in the sample of 100 houses. Or in other words, we can calculate the sample standard deviation for the house sizes across the sample of 100 houses. Thus in terms of calculating the confidence interval, we need to use the calculation that uses the t-statistic rather than the z-statistic. This is because we only know the sample standard deviation, and not the population standard deviation. Let us demonstrate these calculations. So this is the formula that we'll be using. Once again, we use the formula with the t-statistic, because we do not know the population standard deviation. And instead, we'll be using the sample standard deviation. To calculate this formula, we need to first calculate the following expressions. So let's do that, x-bar is a sample mean. So we do =average, open parenthesis. We select the set of data A2 through A101, close parenthesis. So our sample average is 3258.24 square feet. Let's calculate the t-statistic, the t alpha/2. The formula for that is T.INV, open parentheses. The first input is alpha/2. So in our case, alpha is 0.05 because we wish to construct a 95% confidence interval, and alpha is the probability outside the confidence interval. So 0.05 divided by 2. The second input it takes is the degrees of freedom, which is sample size minus 1. So in our case the sample size is 100, so 100 minus 1 is 99. So that's our t alpha/2, a -1.98422. Our sample standard deviation, we can use the STDEV.S function in Excel. Select the data, the set of 100 data points that we have. So that's our sample standard deviation, 957.67 square feet. Our sample size of course, is 100. Margin of error can now be calculated, as the absolute value of the t-statistic. So we could either simply put a positive 1.98, or we could use a function in Excel called abs. So it calculates the absolute value of the number, so abs. We select this calculated t-statistic, multiply it by sample standard deviation. We pick up the sample standard deviation, divided by the square root of sample size. So you could either directly put in 10, which is the square root of 100. Or you could use the sqrt function in Excel, sqrt, the sample size, and close parenthesis. So that gives us the margin of error of 190.02 square feet. We could have also calculated the margin of error using the CONFIDENCE.T function in Excel. So this function CONFIDENCE.T, takes in three inputs. The first input is alpha as it is, which in our case is 0.05. Second input is the sample standard deviation, which we have already calculated there. So we simply pick up that particular cell, in my case cell E14, comma. The third input is the sample size, which in our case is 100. So CONFIDENCE.T, open parenthesis, alpha, then the sample standard deviation, and then the sample size. So this gives us the same margin of error, 190.0226. So you could use either of the two approaches to calculate the margin of error. Now with this margin of error, we calculate the confidence interval, the lower and the upper limits. So the lower limit is nothing but sample average x-bar, minus the margin of error. That gives us a lower limit of 3048.2 square feet. Upper limit similarly is calculated as x-bar which is my sample average, plus the margin of error. So my upper limit of the confidence interval is 3428.3, if you round it off, square feet. Given this confidence interval, she concludes that there is a reasonable probability, a confidence level of 95%, that the average house size in this neighborhood falls in the interval 3048.2 square feet through 3428.3 square feet.