Create a spreadsheet mark book that Mr. Jackson can use to quickly and easily organize and calculate his learners' final marks. Look at the following example:


Mark Book Example

Think about the following questions, and discuss your ideas with your
partner. You might want to write your ideas in a word processor and draw a table that shows how you might organize the information. Keep in mind that you can use make-believe information for now. Mr. Jackson can put the real information in the spreadsheet later.

• For what school learning areas might Mr. Jackson need to keep marks? Pick one learning area to use for this activity.
• What could be the names of the five sample learners who will be listed in the mark book?
• What are some possible marks that learners might have received for five different assignments?
• How could you organize this information?
  

Remember to use the rubric as a guide as you plan, do, review, and share.

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

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

1. Start the spreadsheet software, and open a new, blank spreadsheet.
2. Rename Sheet 1 with the name of the learning area you selected.
3. In cell A1, type a title for the worksheet. Change the look of the title any way you want.
4. In cell A3, type "Learner Name" as that column's heading. Then, in cells B3 through E3, type "Assignment 1", "Assignment 2", "Assignment 3", and "Assignment 4". (You will add the fifth assignment later.) Finally, type "Total mark" and "Average mark" in cells F3 and G3.
5. Change the look of the column headings in row 3.
   
   

Challenge: You might consider rotating the column headings in cells B3 through G3 so they are placed at an angle. Then, add borders around those cells. (For help, see Spreadsheets Skill 5.7: To change the alignment of information in a cell or cells.) (See the Mark Book Mathematics Worksheet Challenge Example at the end of this section.)



6. Change the width of columns B through G to fit the information.
7. In cells A4 through A9, type the names of the five learners. For best results, type each learner's last name, or surname, first. Change the column width, if needed.
8. Type the assignment marks in the four cells to the right of each learner's name.
9. In cell F4, insert a Sum function that will calculate the first learner's total mark. Then, fill the Sum function into the four cells below.
10. In cell G4, insert an Average function that will calculate the first learner's average mark. Make sure the cell range includes only the cells with assignment marks (cells B4 through E4) and not the total mark (cell F4).
11. Copy the Average function into the cells for each of the other four learners, below.
12. Change the average marks so they all have two decimal places. Change the column width as needed.
13. To make sure the mark book will work as it should when Mr. Jackson adds new assignments, insert a column between columns E and F.
14. In cell F3, type "Assignment 5" as the column heading. Then, type the mark marks that each of the five learners received on the fifth assignment. Notice that each learner's total mark and average mark changes all by itself when the new information is added. (NOTE: If you are using OpenOffice.org Calc*, this may not happen automatically. You may have to reinsert the functions to recalculate the values.)
15. Use the learners' names in column A to put rows 4 through 8 in alphabetical or ascending order.
16. As desired, add borders around the worksheet cells.
    
    

Challenge: You might want to make another mark book worksheet for a second learning area. Just duplicate the formatted worksheet, 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 assignment marks for the learners. You should also rename the worksheet's tab. (See the Mark Book Science Worksheet Challenge Example at the end of this section.)



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

Mark Book Mathematics Worksheet Challenge Example


Mark Book Science Worksheet Challenge Example

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

• Title that explains the type of information in the worksheet
  
• Names of five learners sorted in alphabetical order
  
• Formulas that calculate each learner's total mark and average mark
  
• New names on all used worksheets, with all unused worksheets deleted

If any elements are missing, add them now. If you want, make other changes, as well. Remember to save your work when you are finished.

Be prepared to discuss your answers to the following questions:

• How would the worksheet you created make Mr. Jackson's job easier?
  
• What is the most useful spreadsheet feature you used today? Why?
  
• For what other types of activities might you use a spreadsheet's Sum and Average functions?
  
• Is a spreadsheet the most productive tool to use for a mark book? Why or why not?