Exercise 3 - Creating a Spreadsheet Document

Markbook

Many teachers keep track of their learners’ assessment in a markbook. Typically teachers record the scores for different assignments and then use a calculator to calculate learners' marks in a class or subject / learning area. How might you be able to use a computer to make this process easier?

Plan It

Create a spreadsheet markbook based on the assignments and assessment
practices used in your classroom. Look at the following example:

Markbook Example

Think about the following questions, and if working with a partner, discuss your ideas. Writing your ideas on a sheet of paper and drawing a table that shows how you might put the information in order might be helpful.

• What school subjects / learning areas do you teach? For which subject(s) / learning area(s) do you need to keep scores? Pick one class for your markbook worksheet.
• How might you organize the information so it is easy to read and
  understand?

For help on how to do certain tasks, look at the following groups in the
Help Guide:

• Spreadsheet Group 2: Selecting Cells, Rows, and Columns
• Spreadsheet Group 3: Using Worksheets
• Spreadsheet Group 4: Adding and Working with Information
• Spreadsheet Group 5: Changing the Look of Information and Worksheets
• Spreadsheet Group 7: Doing Math
  

Do It

1. Start the spreadsheet software, and open a new, blank worksheet.
2. Rename Sheet 1 with the name of the school subject you selected.
   
3. In cell A1, type a title for the worksheet. Change the look of the title.
4. Now you will add the column headings to your worksheet. Type “Last Name” in cell A3 and “First Name” in cell B3. Then in cells C3 through F3, type information that identifies four of the five assessments and/or
   assignments that you want to include in the grade book. Lastly, type “Total Score” and “Average Score” in cells G3 and H3.
5. Change the look of the headings in row 3.
   
   

   Challenge: Consider rotating the column headings in cells C3 through H3 so they are placed at an angle. Then, add borders around the column heading cells. (See the Grade Book Mathematics Worksheet Challenge Example at the end of this section.) (For help, refer to Spreadsheet Skill 5.7: To change the alignment of information within a cell or cells.)

   
   
   
6. Change the width of columns B through H to fit the information. If your columns are still too wide, change the text wrapping so the information fits on two lines.
7. In cells A4 through A9, type the last names of the five learners. Then in cells B4 through B9, type the learners’ first names. Change the column width if needed.
8. Type the scores for each assessment or assignment in the four cells to the right of each learner’s name.
9. In cell G4, insert a Sum function that will calculate the first learner’s total score. Then, fill the Sum function into the four cells below.
   
10. In cell H4, insert an Average function that will calculate the first
    learner’s average score. Make sure the cell range includes only the cells with assignment scores (cells C4 through F4) and not the total score
    (cell G4).
    
11. Fill the Average function into the four cells below cell H4.
12. Change the average scores so they all have two decimal places. Change the column width as needed.
    
13. To make sure the grade book will work as it should when new grade scores are added later, insert a column between columns F and G.
14. In cell G3, type the name of the fifth assessment or assignment. Then, type the grade scores that each of the five learners received on the fifth assignment.
    Notice that each learner’s total score and average score changes
    automatically when new information is added.
15. Use the learners’ last names in column A to put rows 4 through 8 in
    alphabetical or ascending order. (For help, see Spreadsheet Skill 6.7:
    To sort or change the order of rows based on certain information.)
16. Add borders around the worksheet cells. (For help, see Spreadsheet Skill 5.8: To add borders to cells, rows, columns, and worksheets.)
    
    

    Challenge: You might want to make another grade book worksheet for a second school subject. To do this, duplicate the worksheet you just created and give it a new name. (For help, see Spreadsheet Skill 3.6: To make a copy of a worksheet.) Then, change the worksheet title, and put in new assessment or assignment scores for the learners. You should also rename the worksheet’s tab. (See the Challenge Examples at the end of this section.)

    
    
17. Delete any unused worksheets.
18. Save your work as directed.

Grade Book Mathematics Worksheet Challenge Example

Grade Book Science Worksheet Challenge Example

Review It

Look over your markbook worksheet. Make sure it has the following
elements:

• A title that explains the type of information in the worksheet
• The names of learners sorted in alphabetical order
• Formulas that calculate each learner ’s total score and average score
• New names on all used worksheets, with all unused worksheets deleted

If any elements are missing, add them to the worksheet now. You should also make other desired changes. Remember to save your work when you are
finished.

Share It

Be prepared to discuss your answers to the following questions during
sharing time:

• How might this markbook improve your ability to calculate your
  learners’ grades? How would you modify the spreadsheet you created today for your own use?
  
• What is the most useful spreadsheet feature that you used today? Why?
• For what other types of activities might you use a spreadsheet’s Sum and Average functions?

Next: Proceed to Take Home Exercise

Copyright © 2007 Intel Corporation
All Rights Reserved

Intel® Teach Programme
Participant Version 2.0 (SA) | Getting Started