In this video, we're going to look at calculating confidence intervals. Most specifically, 95% confidence intervals but you can calculate any other confidence intervals. And I mention here on the left-hand size, I've entered 30 values, say that is a group of patients or a group of laboratory results, doesn't matter, I have 30 values there. You can see 9.3. If I click on the little cells, you can actually see that it's more than that. All I've done is I've just selected these two little buttons just to increase and decrease the number of decimal values that are shown. Behind the scenes, this full decimal value is still there. How did I create these values? Well, I just drew them randomly. These are random values from a T distribution. Now, the easiest way to go about this is just to select all of these. I'm going to click on the first cell, A1. Hold Shift+Cmd+ down Enter. This is on a Mac, it selects all of those, we're going to go to data analysis and we're going to choose the script of statistics. The input range is there A1 to A30, the values are in columns, the output range I've already selected this as C1 so it's going to go there. I want the summary statistics with a 95% values. Here's where you can choose different values. And I want the confidence level for the mean also done for me. And there you go. We have the mean, the standard error, the median. There's no mode. With so many decimal values it's unlikely that we would generate at random two values that are exactly the same. We see the standard deviation. And all the way down here we see the confidence level, and that reminds me to tell you there are two difference between LibreOffice Calc, the free version software, and this Microsoft Excel, which is commercial software. In Microsoft Excel, these values here are not dynamic. If I were to realize that one of these values were entered incorrectly and I were to change them, none of these values will update for me, unfortunately. In LibreOffice Calc, they will update. On the other hand, with the LibreOffice.Calc, we don't get this 95% confidence interval. So you'll have to do it by hand and that's what we're going to do here. One reason to do it by hand, in Microsoft Excel, is that if you do want to change one of these values, these values will automatically update. So let's see how to go about it, how do we get this 0.57? Do you remember we're calculating 95% confidence intervals around the mean? So we're going to subtract this value from 10.1. And we're going to add this value to 10.1, which will give us the lower and the upper limits of the 95% confidence interval. But let's look at you would think about doing this by hand. First of all, we're going to calculate the average, equals average. Scroll down to average and hit the tab key here. And at once a list of all my values. So I'm going to click all of them and drag all the way down from A1 to A30. Hit Return or this little tick mark here. And we see 10.1. It's reminded itself that I'm asking for not all the decimal values to be calculated. Now, I'm going to leave these two alone, because that's what we want to calculate. First of all, let's get the standard deviation of the sample, =STDEV.S is for a sample. We will select that. Again, click, hold and drag. Or we could just type A1:A30. We know which those values are. So I see a sample standard deviation of 1.536. And there's my standard deviation here as calculated by the descriptive statistics all the same. The sample size, well, that's easy. In this instance, we know that it's 30, but to do that, let's count. Count is the function that we're going to use, and the arguments that we post A1 to A30. We could also click, hold and drag. How many groups, well, we have a single group of patients or laboratory venues. So we're just going to enter 1 there. The degrees of freedom, remember, is the turtle sample size minus the group size. So it gives us 29. Now, how do we calculate standard area? Now, remember there's standard area up there. We're just converting standard deviation as an area proportion based on the sample size. So that equal standard deviation divided by the square root, SQRT of the total sample size. So that gives us a value of 0.28. That's our standard error. Now, we want 95% confidence intervals so we're going to go for an alpha level of 0.05. And that gives us a probability, that's the interval we're interested in, equals 1- that. Though under the curve is 1, we take away the 5%, so we want 95% confidence intervals. Now, think about that bell shaped curve with the T distribution, the normal distribution. If we talk about 95% confidence intervals, we're leaving 5% out, that's on the very left extreme and the very right extreme that we divide that 5% into 2. So it's 2.5% on either side. And it's important for the function that we're going to use that we know that. Since 0.025 on each side. So we wanted the half of our alpha level, so it's the alpha level divided by 2, that gives us the 0.025. And that allows us to calculate this T probability, which is really what we want. One of these functions, the T.INV that we're going to look at next. Once the value all the way from the left, up the curve to the top, down the curve to the right, that cut off, that leaves only 0.025 on the right-hand side which equals the following, = 1 minus this T half. So it wants that value that's all the way across to the other side. All these functions want different arguments unfortunately. And you can mix some of these up, which will give you incorrect results. So keep a little cheat sheet at hand to remind yourself which arguments go with which functions. So let's look at T.INV, =T.INV, there it is. It asks for probability and degrees of freedom. The probability that it wants is this 0.975 all the way up the hill to the top down the hill on the other side. With only the last of 0.025 left, so this is the value that it wants. And it wants the degrees of freedom, not the samples size, that wants that 29, and we see that we are 2.045 standard errors away from the mean. That is the little mark that we make on X axis, so to get the confidence value, we have to equal this, times the standard error. And if we do that we get the confidence value. The 0.57 which is right there, 0.57. There is this function CONFIDENCE.T which is perhaps the easiest one to use, because it's going to already do the multiplication with the standard error. You don't have to that, so that's the one that I would prefer CONFIDENCE.T, it asks for the alpha value. That's the full alpha value of 0.5, and the standard deviation, which is the standard deviation of the sample, and the full sample size. They're quite different from T.INV, but we get exactly the same result and it's already been multiplied by the standard error. The last one I want to show you just is t.inv.2t. So that's going to do two tell for me. So the probability that it wants, you would think it was going to be 0.95 because the T.INV wanted 0.975, but now it wants the 0.5 value for its probability. And then, it wants degrees of freedom, as well. There you go. And we also have to multiply that by the standard error. There we go and we have 0.57. So there's three ways to calculate 0.57. Easiest CONFIDENCE.T. Just remind yourself every time which are the arguments that you have to pass. They can be slightly confusing. So if were to write a paper we would say that we have a mean of 10.1, the lower bound of the 95% confidence interval is going to be the mean minus our value here. And for the upper bound is going to equal the mean plus 0.57. So it's 10.1 plus minus 0.57. So write it to paper, we could say we have for this group, a mean of 10.1, 95% confidence interval 9.5 to 10.7. And there you go, it's as easy as that. For both Excel and LibreOffice, if I were to change one of these values now, 11.6, we're going to see a dynamic update of our values here. On the Microsoft Excel, it's not going to happen in this automatic creation, but it will do that for you in the greyoffice.com. Enjoy calculating your own confidence intervals.
