A. Introduction | B. Design | C. Design | D. Formulas | E. Functions | F. Conditionals | G. Examples | H. Submitting Your Work
Previous

 

Lab 10 Grades Spreadsheet

D. Creating Formulas by Using Functions

  1. Add Headings.
    1. Sub-total possible. Go to the first row of column "M" (M1) . In this cell, type "Sub-Total Possible". Since it is a heading, follow the instructions on Page C Step 1.

    2. Sub-total earned. Go over to the next column (column "N") and enter "Sub-Total Earned".

    3. Percentage. In the next column over type "Percentage".

  2. Add formulas for possible points.

    1. Sub-total of possible points. Go back to the column with the heading,"Sub-Total Possible", and move down to ROW 4, which should be the first row with your "Points Possible" in it for one of your deliverables. You should now be in cell M4.

      We want to total the points possible for this assignment. There are two basic ways we can do this in Excel. However, both of them require us to tell Excel that we want to use a function. This is done by typing the equal sign = and then typing the function you want to use. When we do this, the function appears in the function box in Excel (see image below). After you press Enter, the function you created disappears from the function box and all you will see in the cell will be the resulting value of the function you created. To edit the function go back to the cell by using either the arrows or the mouse and then click in the function box(see image below).

Function Box Example

      1. Slow method. In this case we can type the equal sign and then simply add the individual cells together. For example, enter =B4+C4+D4. You will notice that as you type in a cell it will change the color of the border of that cell, which helps us know we are typing in the correct cell.
        Close-up view of the Function Box
        Function Example

      2. Better method. Excel has a built-in SUM function. All you have to do is enter:

        =SUM(FIRSTCELL:LASTCELL)

        Make sure you have the colon between them. It will add (or sum) everything between the first cell and the last cell you enter there. So mine might look like this: =SUM(B4:K4).
        Enter means type something in a cell and then press Enter.


    1. Dropping two lowest scores. Your instructor has told you that your two lowest scores will be dropped from labs, quizzes, clicker quizzes, and GoPost discussions. This formula: =SUM(FIRSTCELL:LASTCELL) does not take that into account. These steps will take care of that.

      In general, the formulas will follow this pattern: =SUM(FIRSTCELL:LASTCELL) - POINTS SUBTRACTED

      1. Labs. Subtract the value of two labs (e.g. 2 x 20 points = 40 points) from the total possible. To do this you can simply add onto your function: - 40.

      2. Clicker Quizzes. Subtract the value of six Clickers (e.g. 6 x 5 points = 30 points) from the total possible. To do this you can simply add onto your function: - 30.

      3. GoPost Discussions. Subtract the value of two GoPost Discussions (e.g. 2 x 15 points) from the total possible. To do this you can simply add onto your function: -30.

  1. Add Formulas for Earned Points

    1. Add formulas to total scores. Move to Cell N5. Add formulas to add the points earned in this row for this deliverable. Repeat ths

    2. Dropping lowest two scores. To drop your two lowest scores, move them to the drop row.

  2. Add Formulas for Percentage

    1. Finally, we want Excel to calculate the percentage of points you earned out of the total possible. To do this you will use the general fomula for finding a percentage but will replace each of these items with cell references, like =A3/A4.
      / represents "divide by" in math. Use it to divide in Excel, too.

      "Percentage" = "Sub-Total Earned" / "Sub-Total Possible"

    2. Make sure your percentage function is on the same row as your "Points Earned", but in the column where you previously placed your heading "Percentage" (see image below). Thus, place this function in cell O5 as is shown in the image below.

    3. Change data type to percentage. You may have noticed that you got a decimal (e.g. .625) instead of a percentage (e.g. 62.50%). Let's tell Excel that you want the information in that cell to be treated as a percentage.

      1. Go to the "Number" menu as seen in the image below and click on the drop-down menu. It will likely say "General" as shown in the image below. Change it to "Percentage". Alternatively, you can right click on the cell and choose "Format Cells" and under the "Number" tab click on "Percentage". Decide the number of decimal places as well. Usually 2 is a good number but you may decide that you don't need any as well in this particular instance. You can also change the number of decimal places by clicking on the little icons in the small red oval in the image below.

Number Example

    1. Repeat until you have entered all of the formulas for each type of assignment. Always make sure you are in the correct column and row when doing it.

               5. Go to Lab 10 WebQ and answer questions 4 and 5.


A. Introduction | B. Design | C. Design | D. Formulas | E. Functions | F. Conditionals | G. Examples | H. Submitting Your Work
Previous