Excel statistics essential training: 1 exam answers

“

- [Instructor] In this course, we'll use Excel as a teaching tool to help solidify our understanding of statistical concepts. But before we can do that, let's make sure we're on the same page with simple Excel capabilities. I want to start with some simple functions. So here, I have two columns of numbers labeled X and Y, and a third column labeled Y minus X. The third column will give the difference between the numbers in each X-Y pair. Let's start with the sum of the Xs, which I can calculate in E12. Here's where I'll insert the sum of all the X numbers. I can do this in a couple of ways. Here's the first way. I click on the Formula's tab and then on Math and Trig. Scroll down the dropdown menu and select Sum. That opens the sum dialogue box. The number box you see that Excel has guessed the numbers I want to add up. It's not always right, but this time it is. Notice that the answer, 369, is already in the dialog box. I click OK, and the answer appears in the selected cell. Take a look at the formula bar. It shows equal sum and in parentheses, the numbers we added. Sum is the function and the numbers in parentheses are called arguments. I'll delete the answer and show you another way. When you become proficient in Excel, you can type the function directly in the cell without using the dialogue box. Select the cell, type equal, then sum, and then the left parenthesis. Next, select the numbers you want to add up. Select the first one, hold down the shift key, and select the last one. Then type a right parenthesis. Press enter. The formula looks just like it did before in the formula bar. You can also enter the some formula and drag over the range you want to sum. I'll delete the answer and show you another way. When you become proficient in Excel, you can type the function directly in the cell without using the dialogue box. Select the cell, type equals, then sum, then a left parenthesis. Next, select the numbers you want to add off. Select the first one, hold down the shift key, and select the last one. Then type a right parenthesis. Press enter. The formula looks just like it did before in the formula bar. You can also enter the sum formula and drag over the range you want to sum. It's important to keep in mind that all formulas begin with an equal sign. To get the sum of the Y numbers, all I have to do is select the cell with X number sum, move the cursor to the lower right corner until a small plus sign appears, and this is called the fill handle. Hold down the left mouse button and drag it to the cell below the Y numbers. The answer appears in that cell. And if you click on that cell, you'll see the formula appear on the formula bar. Statisticians often have to square each number in a set of numbers, and then add them up. Excel has a function for this. Select the cell just below the one that holds the sum. Select Math and Trig from the formula tab, and then sum square or SUMSQ from the dropdown menu. This time, the dialog box guesses wrong. So select cell E2, hold down the shift key and select E11 and click OK. You see the answer in the selected cell and the formula in the formula bar. As before, drag the answer into the adjoining cell to get the sum of squares for the numbers in the Y column. Another thing statistician sometimes have to do is find the sum of products of columns of numbers, meaning they find the product of each X-Y pair, and add all the products together. Excel has a function for this too. Select a cell for the answer F14 and select sum of product, then Math and Trig drop down menu. In the array one box, select or enter the cells in the X column, press the tab key on your keyboard, and in the array tool box, select or enter the cells in the Y column. We're not concerned with array three, and you can have up to 255. Click OK, and the answer appears in the selected cell. In that third column, we'll put each Y minus X. The simple way to do this is to select the first cell and type equals, and then select the first Y number, type minus, and then select the first X number. Press enter, and the answer appears. You can then fill the rest of them in by dragging down as we did before, until you get to the last X-Y pair, I'll delete all the numbers and show you one more way. First, select the entire array, the range in which the differences will appear. Then type equal and that select all the numbers in the Y column. Click the first one, hold down the shift key and select the last one, then type minus. Then select all the numbers in the X column. Finally, and this is important, press these three keys on your keyboard, control, shift, and enter. The answers appear in the cells. This is called an array function. In the formula bar, notice the curly brackets around the formula. They signify array function. Now that I've shown you how to use simple functions in Excel, let's move forward to the statistical functions.