Computer Science 1300
    Essential Computer Skills

    Fall, 1998

    Curtis Brown
    cbrown@trinity.edu

    Lab 13

      This is a spreadsheet lab using Microsoft Excel.  Here's what you need to do:

      1.  Get a workbook I have prepared containing some headings and data for a gradebook (for an imaginary class).  (Apologies for the choice of subject -- I realize that making a gradebook is probably more useful for me than for you!  However, many of the things you'll need to do should generalize to a wide variety of other projects.)  You can get the workbook by going to our class folder and dragging the Excel workbook called "starter" to your own computer or network folder.  After you obtain the workbook, open it in Microsoft Excel.  It should look like this:

      2.  Now you need to make a number of changes and additions.  First, all the headings in row 2 should be in boldface and centered in their cells.

      3.  Headings in column 1 should be in boldface.

      4.  Columns should be wide enough to show all the text they contain, but should not be wider than necessary.  (Format:Column:Autofit Selection is useful for this.)

      5.  In cell B18, write a formula that will find the high score on the first assignment.  Copy the formula and paste it to the row 18 cells in columns C, D, E, F, and G.  (Don't type in the formula over and over again by hand!)

      6.  In cell B19, write a formula that will find the low score on the first assignment.  Copy the formula and paste it to the row 18 cells in columns C, D, E, F, and G.  (Again, don't type in the formula multiple times.)

      7.  In cell B20, write a formula that will find the mean score on thefirst assignment; copy and paste to the row 20 cells in columns C, D, E, F, and G.

      8.  In row 21, do the same thing with the median score.

      9.  In cell H6, write a formula that will calculate the final grade for a given student in percentage form.  This will be a little tricky.  The grade should be expressed as a number between 0 and 100.  It should represent a weighted average of the percentage score on each assignment.  The percentage score on a given assignment will be (points achieved/points possible) * 100, where "/" means "divided by" and "*" means "multiplied by".  The weighted average can be determined by multiplying the percentage score for each assignment by the weight for that assignment, and adding up the results for all assignments.  Note that the total points possible for each assignment is given in row 3, and the weight for each assignment in row 4.

      Write the formula in such a way that you can simply copy it to the other cells in column H and it will still work correctly.  This will probably involve using absolute row addresses for points possible and for weights.  Once you have the formula working correctly, paste it to the other cells in column H.  (One way to check whether your formula is working correctly is to paste it to H3.  If you have the right formula, it should give you a value of 100 for this cell -- someone who got the total points possible for every assignment should end up with a final score of 100!)  Make sure that the formula will continue to work if you change the weights or the points possible -- you'll be doing another version of the workbook with different weights, and should not need to change anything other than the weights themselves for the revised version to work correctly.

      10.  In cell I6, write a formula that will convert the final grade from a percentage to a letter grade.  Don't enter the letter grades by hand!  Write a general formula that will do the conversion for you.  This one will also be somewhat tricky.  Write the formula so that it will convert the percentage to a letter in accordance with the following table:
       

      94-100 A
      90-93 A-
      87-89 B+
      83-86 B
      80-82 B-
      77-79 C+
      73-76 C
      70-72 C-
      < 70 D
      (Don't worry about D+, D-, or F.  You'll need to use logical operators in your formula.  You'll need to enclose letter grades in quotation marks in the formula so that they will be treated as text.  And you will most likely need a number of layers of embedded formulas.  Note that you can only embed up to seven layers of formulas -- that's why we aren't worrying about D+, D-, and F!  I'll be looking at your formulas with interest, to see whether you come up with a better solution than I have.)

      11. Format the resulting table so that it looks at least moderately attractive, and so that divisions between different sorts of data are clearly demarcated.  Once all the formulas are in place and the table is formatted, a printout might look something like this:

      (This version has a heading, and the names have been alphabetized.  You don't need to do these things, as they weren't part of the original assignment.  On the other hand, they're easy to do and improve the appearance, so you might want to just for kicks!)

      (Note:  this is the new version.  In case you were wondering what was wrong with the old version . . . my formula for "mean" averaged the entries from rows 16-16 rather than 6 - 16!  So in each case the supposed "mean" was really just the last student's grade.)

      12.  Save the lab as YourName-1.  Then change the weights to the following values:  exams 1 - 3, .15; final exam, .15; first paper, .1; second paper, .3.  Save the resulting workbook as YourName-2.

      13.  Turn in both versions of the workbook by placing them in the Lab13 subdirectory of our class folder.
       
       

      Due:  Thursday, November 19.

      Last update:  November 17, 1998 
      Trinity University  | Curtis Brown  |  CSCI 1300:  Essential Computing Skills