Winter 1998 Computers and Society
GST 2710, Section 90571
AGS 3360, Section 95198
Last updated: 4/21/98
Link back to course Welcome...

In-class Spreadsheet Worksheet

1. Start Microsoft Works and start a new spreadsheet. Notice that, at least at first, the spreadsheet screen looks a lot like the List view of a Works database.
   1. Click in cell A1 to select it. Type a title for the Spreadsheet and tap <Return>. This will be a spreadsheet to calculate your grade for Quiz 6, so choose something appropriate.
   2. In Column A, starting in Row 2 and working down, type a label for each question in Quiz 6: 1A, 1B, 2A, 2B, 3, 4A and 4B.
      1. If you enter just "3" for question 3, spreadsheets will interpret that as a number instead of text, and left-justify it instead of right-justifying. To force Works to treat 3 as text, type '3 instead.
   3. Enter your grades for each question. If you do not have the quiz with you, enter numbers between 0 and 100.
   4. Just below 4B, type Avg 1, and continue on down with Avg 2, Avg 3 and Avg 4. Avg 1 will be the average of 1A and 1B, and so forth. In grading the Quiz, I find the average grade for each question, and then average the averages. In the cell underneath Avg 4, type Grade.
   5. We will use the following functions:
      1. avg(range), which computes the average of a range of cells. For example, the range of cells from B3 to B7 is indicated by B3:B7.
      2. round(x,n), which rounds the number x to n decimal places. Your grades are rounded to zero decimal places.
   6. For the question averages, move over one column to Column C. This is common practice in budgeting, for example. To enter the formula for Avg 1, click in the cell in column C (not B) that is opposite Avg 1, and
      1. Type =avg(
      2. Click on the cell with the grade for 1A and drag down to the cell with the grade for 1B. Notice that this puts the locations of the two cells after the left parenthesis with a colon between them. The colon means "and all the cells in between".
      3. Type ) and tap <Return>. The average should appear.

      [If you took the makeup for Quiz 6, there was only one part for Question 1. Therefore, treat Question 1 like question 3 below, and apply the directions above for Question 2.]

   7. Repeat for Avg 2 and Avg 4.
   8. Click in the cell in column C that is opposite Avg3 and
      1. Type =
      2. Click in the cell with the grade for question 3
      3. Tap <Return>. The grade for question 3 should appear.
   9. With the grades for each question in a row, the next step is to enter the grade on the quiz, the average of the averages. For this, move over one more column. Click in the cell in column D (not B or C) opposite Grade and
      1. Type =round(avg(
      2. Click on the cell with the Avg 1 grade and drag down to the cell with the Avg 4 grade
      3. Type ),0) and tap <Return>.
   10. The final calculation should agree with the grade on your quiz paper. Save the file if you want to keep it, and close that window (do not close Works, just the spreadsheet).
2. Choose File / Create New File... and start a new spreadsheet. This spreadsheet will graph the class grades for Quiz 6.
   1. Type an appropriate label for this spreadsheet in Cell A1.
   2. In Cell A3 type Grades
   3. Starting in Cell A4 and working down, type in the individual grades for Quiz 6: 54, 94, 45, 100, 67, 88, 97, 87, 97, 64, 81, 85 and 94 (these grades are NOT in alphabetical order, so individual privacy is respected here).
   4. Below the bottom grade, calculate the class average using the avg formula.
   5. Drag over the individual grades (do not include the average) and, with these cells selected, choose the Tools / Sort Rows menu item.
   6. In the "Sort Rows" dialog, check that "1st Column" is set to "A", and click OK. This will sort the grades in ascending order. Notice that the average does not change.
   7. Do the sort again, in descending order.
   8. Now we will make a bar chart of the grades, counting the number of grades between 90 and 100, 80 and 89, 70 and 79, etc.
      1. In Column C, opposite the first grade, type 40-49. Working down, type 50-59, 60-69, etc., ending with 90-100.
      2. Count the number of grades in each range and type the number in Column D opposite the label for the range. (The "big" spreadsheets such as Excel have functions to do this counting for you.)
      3. Drag over the cells with the ranges and the counts in both Columns C and D. Start in one corner and rag to the opposite corner.
      4. With that range of cells selected, choose the Tools / Create New Chart menu item.
      5. Click OK to accept the bar chart.
      6. With the bar chart displayed, select the Format menu item and unclick "Add Lengend".
      7. To label the graph, select the Edit / Titles... menu item.
      8. In the "Titles" dialog, enter
         1. Chart Title: Quiz 6 grades
         2. Horizontal (X) Axis: Grades
         3. Vertical (Y) Axes: Number
   9. Now try a pie chart. Select the Window menu item, and then the window with the grade data in it.
      1. With the grade data showing, select the Tools / Create New Chart menu item.
      2. On the pull-down list "What type of chart do you want?", click on the down-arrow at the right and select "Pie". Click OK to accept the pie chart.
      3. With the chart showing, select the menu item Edit / Data Labels...
      4. In the Data Labels dialog,
         1. Make sure that 1st Label is set to Cell Contents
         2. Make sure that Cell Range is set to the cells in Column C that have the grade ranges
         3. Set 2nd Label to Percentages
         4. Click OK
      5. If you Save As..., the charts and the data sheet will be saved as a single file.