BTT101 SPREADSHEETS

Markbook

All of these activities mean teachers need a better markbook. Yup, it's your turn to create one to help us out. We'll leave out the final assessment for now. Use five worksheets.  One each for Knowledge, Thinking\Inquiry, Communication and Application.  On each worksheet create a heading, at least five assignments, projects or tests, each with their own denominator ("out of" row or column) and add ten of your fellow students (let them all pass, please!).  On the fifth worksheet show the final mark and a summary of each category and it's portion of the final mark.   The breakdown should be 50% for Knowledge, 10% each for Thinking\Inquiry and Communication and 30% for Application.  Create a graph on that page as well.  Below is an example for one worksheet:

KNOWLEDGE
            Mark:  Test 1   Asmt 1    Total
          Out of:      30       15       45         %
Student A              14       14       28     62
Student B              16       11       27        60
Student C              17       12       29     64
Student D            etc.

Now the same spreadsheet with formulas to determine total and percentage for the knowledge category:

KNOWLEDGE

                          Mark:    Test 1          Asmt 1       Total
          Out of:      30       15        45           %  
Student A              14       14    sum formula  =E4/$E$3
Student B              16       11    sum formula  =E5/$E$3
Student C              17       12    sum formula  =E6/$E$3
Student D            etc.      etc.       etc.       etc.

You will notice above the absolute reference to the total in cell E3 (45). The fifth worksheet will incude the last (%) column from each of the other four worksheets. You will have to have to create long formulas for the fifth worksheet to reflect the weightings of the categories. The fifth worksheet should look something like this:

In this example we have shown the marks pasted for student A and the formula view for the same marks pasted for student B. When you do yours you will see the marks only until you check to see if you have used the paste special function properly.

The final marks formula, which you can do once and then fill down, will look something like this for student A:

=B3*$B$2+C3*$C$2+D3*$D$3+E3*$E$3

While the formula may be long, you only need to create it once! There are other ways to find the final mark.  Be creative!  Make sure you have links from the first four worksheets to the fifth (Final Mark) worksheet. You can save time by copying the student names from the first worksheet to the rest. Make each worksheet graphically pleasing with easy to read fonts and colours. Save as marks. Good luck.