MathCS.org - Statistics

back | next

2.3 Calculations with Spreadsheet Data

Excel can perform a wide variety of operations with the data you entered, but in this course we will only need a small subset of all available functions. This section will explain - very quickly - how to enter some of the more commonly used functions.

There are many different methods in which Excel will let you perform the operations described here; we will usually give only one of the possible methods. If you already know a more efficient way to accomplish the same goal, use your method.

Example: Suppose I want to find the sum and the average of some tabular data, for each row separately.

First open Microsoft Excel and enter the data into the spreadsheet, as follows:

To compute the sum of the numbers in column C, proceed as follows (if you follow these instructions slowly and carefully it should work just fine):

  1. We assume you have entered the number 10 to 90, as shown in the above picture. Now click on the cell C4 which is going to contain the sum of the numbers in the C column.
  2. Type:
       =

    to indicate that this cell will contain a formula
  3. After the equal sign, continue to type:
       sum(

    to specify that you want to use the summation function - make sure to include the open parenthesis (
  4. Now use the
       Up-Arrow
    key to move the active cell to C3. Note that C3 will automatically be placed in your function
  5. Hold down the
       SHIFT
    key and press the Up-Arrow twice
    Now C1:C3 will be placed in your function
  6. Complete the function by typing
       )

    (the closing parenthesis) and hit ENTER

You should now find the sum of the cells C1, C2, and C3 in cell C4. Instead of using the arrow keys in steps 4 and 5 you can also select the cells whose sum you want to find by "dragging" the mouse over them. In other words, you could also:

  1. Click on the cell C4 which should contain the sum of the numbers in the C column.
  2. Type:
       =

    to indicate that this cell will contain a formula
  3. Continue typing:
       sum(

    to specify that you want to use the summation function - make sure to include the open parenthesis (
  4. Use the mouse to drag the cursor over the cells from C3 to C1.
    Now C1:C3 will be placed in your function
  5. Complete the function by typing
       )

    (the closing parenthesis) and hit ENTER

which will accomplish the same task. Now your spreadsheet should look similar to the following:

Instead of enter the final two formulas into cells A4 and B4, we can now copy and paste the formula from cell C4. The range of the summation formula will automatically adjust to compute the sums of columns A and B, respectively.

  1. Click on cell C4

  2. Select "Edit | Copy" or use the Control-C keyboard shortcut

  3. Click on cell B4 and select "Edit | Paste", or use the Control-V keyboard shortcut

  4. Click on cell A4 and again select "Edit | Paste", or use the Control-V keyboard shortcut

Excel offers many other functions that are interesting for statistical analysis, such as:

Practice: Compute the average of the numbers in columns A, B, and C (of course do not include the sums in the fourth row in your calculation). Make sure the average for each column is computed below the sum. Then format the sum and average numbers bold and italics.