Lukas's Mark Spreadsheet
As with many teachers in the TCDSB, I've been frustrated by the lack of an easily accessible and flexible mark recording software. So early in my career, on my own time, I decided to develop my own as a Google Spreadsheet. I've been updating it for years and have recently started sharing it with other teachers who have asked me about it.
Here is the copy link if you want to check it out: Mark Spreadsheet Copy Link. (Clicking this link opens up a page where you can create a copy of the Sheets file in your own Google Drive. If it's not working try signing into your own Google Drive first)
Check out the tips/updates tab for some more info on the spreadsheet and how it works.
Here is the copy link if you want to check it out: Mark Spreadsheet Copy Link. (Clicking this link opens up a page where you can create a copy of the Sheets file in your own Google Drive. If it's not working try signing into your own Google Drive first)
Check out the tips/updates tab for some more info on the spreadsheet and how it works.
Features...
- Easy to access and use, it's just a Google Sheet!
- Individual report generated for each student which can easily be printed off
- Marks less likely to be skewed due to use of denominator-based calculation*** see explanations below
Disclaimer...
- If you want to use this spreadsheet for your own use, I am not responsible for any errors or issues you have. So use at your own risk! As long as you don't modify any of the formulas though it should work! The YELLOW and BLUE cells should be the only cells you modify (unless you know what you are doing with spreadsheets and formulas). If something does go wrong, you can always UNDO or revert to an earlier version of your file using VERSION HISTORY.
- The most common issues people run into are putting a comma instead of a decimal for a mark (like 1,5 instead of 1.5). This will cause marks to not be calculated. The other major issue is if you insert or delete columns or rows. There are formulas in a lot of the cells and if you delete a row or column it can throw everything off. If you need to add additional students, add them at the bottom of the list instead of adding a new row. You can then drag that entire row into its correct place by clicking and dragging the row name on the left. You CAN delete an entire row for a student if they are demitted from a class, but NEVER delete a column! If there are columns you want to hide, you can right click on the name of the column at the top and select hide column. Sorry about that but if I was paid to develop this I would make it all foolproof but this has been a side project for me :)
- The way this spreadsheet calculates a student's mark is NOT the same as how Webmarks or Markbook calculates the mark. Both of these use "percent-based marks" instead of "denominator-based marks". So if you aren't comfortable with this, please don't use this spreadsheet. I have concerns about how percent-based marks can be significantly skewed, especially when adding the compounding factor of category weightings and activity weightings. If you are interested in this see below for a PDF with a deeper explanation of my concerns with this. I put together years ago with a colleague and shared it with the board when they asked for feedback on Webmarks. I explain below what is different...
| skewed_marks_in_web_marks.pdf |
What is different about the way your spreadsheet calculates marks?
- My spreadsheet uses a denominator-based approach. Meaning if you have an assignment that a student gets 5/8 on and then another that they get 15/20 on, the overall mark is just the sum of the numerator over the sum of the denominator: 20/28 or 71%, as opposed to 69% that the student would get if it were percentage-based ((5/8+15/20)/(1+1)). Note that the denominator-based approach will not always be lower, it could go either way, but if you avoid the pitfalls that can skew marks for either method, the overall mark should be very similar for either method.
- The advantage of using this method for calculating marks is that it is much, much simpler for students, parents, and teachers to understand. Overall mark = total marks earned over total possible marks, easy! As opposed to percentage based marks where a student can bomb one category on an assignment, ace the rest of it, and then their overall mark drops. Explaining this to students was a bane of my existence when I used it. With percentage-based marks, I just find it much less intuitive to prevent marks from being skewed in instances such as this. You have to plan out activities your whole semester ahead of time to ensure the weightings are reasonable for each activity. You and I know this isn't how classes usually go. Things change on the fly and we have to be reactive to the students in our classes.
- Considerations: Marks can still be skewed with a denominator-based calculation. To avoid this, just ensure tests, quizzes, and assignments have roughly proportional number of marks assigned to each of them. For example if quizzes have on average 20 marks, tests have 40 marks, and assignments have 15 marks, that will automatically determine how much each activity is weighted. So tests will have double the weighting of quizzes, and assignments will be a little less than quizzes. If you have an assignment that is worth 100 marks, it would be worth 2 and half tests in this case, so unless this is what you're going for, make sure to avoid it.
- BUT you can still apply a weight factor to each activity separately! So if you do want an assignment to be out of 100 but want it to be worth the same as a quiz, you could add a weight of 20/100 or 0.2 to that assignment to make each mark on the assignment worth 1/5 of what it would be on the test. This would make the balance between the test and the assignment more reasonable. Mostly I leave the weighting factor as 1 for each activity and then adjust down or up for any activity that is worth too much or too little from there. There is no restriction on what the weighting factor could be and the % of the overall mark that the activity is worth is displayed at the top with each activity.
- What about KTCA? As per growing success, we're required to assess K, T, C and A. My spreadsheet accommodates this as well. Marks are put in each of those categories and then how a student is doing in each of those categories is displayed beside each student. The difference is that for my spreadsheet, the categories aren't multiplied by a weighting that is specified by the teacher. The weighting comes OUT of how many marks you've assessed in each category. So for example, in a course if you've asked a total of 30 marks for K, 10 marks for C, 10 marks for T, and 40 marks for A, you have a total of 90 marks for the course. This means the K is 30/90 = 33% of the term mark, C and T are 10/90 or 11% of the term mark, and A is 40/90 or 44% of the term mark. So it's up to the teacher to monitor the balance of the marks asked in each category as the course goes on. This is a much more honest approach to marks than to arbitrarily assign weightings to each of the category, which has a tendency to skew marks.
