Microsoft Excel, part of the Microsoft office suite, provides software tools
that will simplify your statistical computations. Previously i taught you
about summation, an important first step in statistical calculations, especially
when you're working by hand using formulas. In this tutorial, you will learn
how to use Excel to summarize a data set for use in a formula. Plus, I will teach
you some hints and tricks for using Excel. Summation refers to adding up all
of the numbers in a column. In Excel, summation uses the formula "=SUM." Of
course, to add up a column of numbers, you need some numbers in a column, something
that we call a data set. Creating a data set gives you a great opportunity to
practice data entry. We will imagine that we have just sampled a classroom of
statistic students who have completed a 30-point quiz. On sheet1, in cell a1, type
QuizScores as the name of this column of data, later on "quizscores" will be our
variable name. Beginning in cell a2, enter the data from the following list into
column A. Your last score will be in cell a28. Once you have finished entering the
data, save it to your computer desktop as quizscores.xlsx. Beginning in cell
a2, enter the data from the following list into column A. Pause the video to
enter these data. So now we have all of our data in a
single column. But oops, it turns out you need this numerical data in two columns
instead of just one. No worries, we will just cut and paste. First however, let's
go to a new worksheet. At the bottom of the page you will see tabs for sheet1,
sheet2, and sheet3. If you do not have three tabs, click the plus sign to add
more. Now click on each tab, and notice the sheets are blank. Return to sheet
number one where you have already entered data. In sheet 1, hover your mouse
over the a in column A. Click on the A to select the entire column. Right-click (or
control-click on Mac) anywhere on the highlighted cells and from the drop-down
menu choose "copy." Now click on the tab for sheet 2 at the bottom of the page.
Position your cursor in cell a1, right-click, and from the drop-down menu
choose paste. Now let's change some titles. Where it currently says quiz
scores, change cell a1 to a capital X. Now add titles across the next five columns.
Column B is capital Y, column C is X2, shorthand for x squared, you could
actually make the two a superscript, but that is not really necessary for now.
Column D is Y2 (again don't bother with a superscript), column E is X-Y (for X minus Y),
and column f is XY (for X times Y). Whenever variable names such as x and y
appear with no mathematical notation between them multiplication is implied.
Next, highlight just the numbers from cell
a16 to a28 and cut to them. Using edit > cut, we'll highlight the cells to be
surrounded by a marching ants marquee. Notice how the little dots look like
ants marching around the boxes. Select cell b2 and paste those scores, using
edit > paste. Next, add a score. Because you have one less score in column B in cell B15,
type the score 23. You should now have an x and y column each with 15 scores.
Your new data set is complete for now, save your work as quizscores.xlsx.
What is the mathematical notation for using Excel formulas? Well as you might
expect, subtracting columns uses the minus sign, while adding columns uses a
plus sign. Multiplying uses an asterisk, shift 8, and dividing uses a slash.
Squaring is multiplying a number by itself. You could use an asterisk to
square a cell, or use the cell reference followed by carrot 2. I'm going to show
you a trick for copying a formula from one cell to another using dragging, but
first, let's create some data. Creating the x-squared column click in cell c2.
Enter the formula =A2*A2,
then tap the Enter or return key. The number 841 will appear in the cell. So
here's that dragging trick. Click on cell c2. When you click on a cell to select it,
a small blue box appears in the lower right corner of the selected cell. The
color of the box may be black or green depending on your version of Excel, but
that box is a handle that can be dragged to copy the contents of that cell to
other cells. Hover the cursor over that small box until the cursor turns into a
cross bar, that looks like a thick plus sign, click, hold and drag the c2
handle down to cell c15, to copy the formula to other cells Creating the y
squared column: Click in cell d2. I'm going to teach you two tricks. The first
is a shortcut to typing. Type = and now click on cell b2. The cell
reference is added for you. Next, a better way to square a number. Type the carrot
(shift 6) and the number two. Then tap the Enter or return key. The
number 324 will appear in the cell. Hover the cursor over the small box in the
lower right corner of d2 until the cursor turns into a plus sign, and here's
another trick. This time double click to copy the formula down to D15 and
complete the column. Creating the X minus y and x times y columns - click in cell e2.
Enter the formula = A2-B2 by clicking on the reference
cells instead of typing their names. Now hit the enter or return key/ The number
11 will appear in the cell. Do not drag to copy the formula just yet. Click in
cell f2 enter the formula =A2*B2 by clicking on the
reference cells. Tap the enter/return key; the number 522 will appear in the cell.
Now click on E2, don't let off the mouse button, hold and drag to the right
to highlight cells e2 and f2. Now release the mouse button. Hover and when
the cursor turns into a plus sign, double-click. This will copy the formulas
from both cells down both columns. Summing it up: summation is an Excel
function that works like the formulas we have already created. It begins with an
equal sign then the word sum, =SUM, it references the cells to be summed and it
can be dragged or double clicked to copy. Let's sum the X column. Create a new
formula in a16 that adds up the numbers in that column. Click in cell a16 and
enter the formula =SUM(A2:A15)
Tap the enter or return key. The number 352 will
appear in the cell. Now here's another way to sum columns. Click in cell b6.
Type an equal sign, and look in the top left corner of the screen, to see the
Excel suggestion, SUM. Click on the arrows next to SUM, and a dialog box
will open suggesting that you SUM B2 to B15. Click OK, the number 321 will
appear in that cell. Now let's sum the remaining columns. Click on cell B16.
This time we're going to drag the contents, however we can't double click.
So grab that little handle in the lower right corner, and drag the contents to
the right over the remaining columns to Cell F16. This will calculate the sum of
the remaining columns. Let's make the data bold. We'll change the font for the
summations to bold just to keep them distinct from the other data. Hover your
cursor over the number 16 on the left edge of row 16. The cursor will turn into
a rightward facing arrow. Click to select the entire row, 16. It will change color
to indicate that it is selected. Now click the bold button, or use ctrl + B, (or
command + B on mac) to change the font to bold. And finally, squaring summations.
Although you created the numbers in cell a16 and b16, you can still square those
numbers. You can do formulas on other formulas. So click in cell a17 and
create this formula, =A16^2. Tap the Enter or return key.
The number 123904 will appear in that cell. Drag that formula to cell A17
in order to create the sum of y squared, and then make this row bold. If
you follow along with the video about doing summation by hand, isn't this a
much easier approach using Excel? We are going to use these data again and import
them into SPSS, and you will want to know where the file is, so close this data set
after saving it on your desktop as quizscores.xlsx.