Some of Excel's built-in functions are specifically geared to statistical work. In this video, learn how to use these helpful functions.
- [Instructor] Let's learn about Excel's statistical functions. You can find them in a drop down menu. In the spreadsheet, 01_02, you'll find a data set we can use to run some statistics. First we'll calculate the average of the X numbers. Select cell E12, and on the formulas tab, find the statistical functions. Unfortunately Excel doesn't keep them at the top level. Click more functions, and then statistical from the dropdown menu. This opens another dropdown menu. Select average. The average dialog box opens and Excel has guessed correctly that we want to average the numbers in cells E2 through E11. The answer is already in the dialogue box and when we click, okay, the answer 77.3 goes into cell E12. A look in the formula bar shows you the formula that you could have manually entered it to cell E12. Now we'll calculate another statistic for the X numbers. It's called variance and it measures the spread of the data around the average. We discussed this one and the next one, standard deviation, and what they're all about, much later. For now, we'll just use Excel functions to calculate them. Same procedure as with average. But now we find VARP in the statistical menu. P stands for population. This means that these 10 scores are the only ones we're interested in. They're not a sample from a larger population. They're the whole population as far as we're concerned. This time in the dialog box, Excel has guessed wrong about the cells to work with and we have to make a correction, so correct that to E2 through E11. Click Okay. And the answer shows up in the selected cell. Also, you can just type =VARP and highlight the range. The next statistic is standard deviation. To calculate it, we select the cell, find STDEVP in a dropdown menu, correct the cells to work with, and click okay. How can we quickly calculate all these statistics for Y? Select all three statistics we just calculated, move the cursor around the bottom cell of the three and to the lower right hand corner until the fill handle, the little plus sign appears, hold down the left mouse button and drag into the next column. You now have those three statistics for Y. The next statistic is called the correlation coefficient. It's an indicator of how the numbers in X are related to the numbers in Y. We learn about this one in depth much later in the course, but now we'll just calculate it. Select, cell F15 and find CORREL in the statistical menu. In the CORREL dialog box with the array one box active, select cells E2 through E11. with the array 2 box active, select cells F2 through F11. Click okay and there's the answer in the selected cell. To show that the correlation encompasses both X and Y merge cell F15 with cell E15. Select both cells, and on the home tab in the alignment area, select merge and center. This does the trick. Next, we look at standard scores, another topic for later in the course. When we standardize a set of scores, we take each score, subtract the average of the scores and divide the difference by the standard deviation. The statistical function, standardize, does the work for us. Select cell H2 for the standard score for the first X number. Then find standardize in the statistical menu, and select it to open the standardized dialog box. With the X box active, click cell E2. Now with the mean box active, click cell E12, mean is another name for average. With the standard deviation box active, click cell E14. Click okay, and the answer is in cell H2. Let's see what happens when we drag down to fill in the remaining standard scores. As you can see, it gets ugly. The reason for this is that when we drag into the cell below Excel thinks we want the means to be in the cell below where the mean was, and that we want the standard deviation to be in the cell below where the standard deviation was. And this error continues as we drag into the next cells. What we need is a way to anchor cell E12 for the mean and anchor cell E14 for the standard deviation. We do that with the F4 key on the keyboard. And it's command plus T on a Mac. So just go into the formula bar, click on E12, the F4 key, E14 and the F4 key, and the dollar in front of the E stabilizes the column and the dollar in front of the 12 stabilizes the row for the mean, and the same thing for the standard deviation. Now, when we enter, we can drag down, and there's our standard scores.
- Explain how to calculate simple probability.
- Review the Excel statistical formulas for finding mean, median, and mode.
- Differentiate statistical nomenclature when calculating variance.
- Identify components when graphing frequency polygons.
- Explain how t-distributions operate.
- Describe the process of determining a chi-square.