Knowing Where They Are
As we move towards linear assessment, in year marking and the awarding of grades is going to become increasing more important to guide students and have a gauge on progress. Here is an excel sheet that I have developed this year to help staff gain an overview of student progress within their department that can then be used within discussions with their line managers to address emerging themes across cohorts, departments and faculties.
I use this within the context of a large sixth form college where many courses have >150 students on and there are few with <40 students. This sheet comes with the following health warnings:
- This should not be used to make students grades match three year trends or experience extend grades.
- With small cohorts (<50) trends and patterns are less reliable.
- No-one knows what the profile of grade boundaries will look like under the linear system yet.
It can be a useful tool to review where a cohort is mid-year following assessments. I encourage departments to use this with each assessment to get any overview of the spread of results that they have given in comparison to expected outcomes and three-year trends to give a pulse measure. Further to this, I expect them to complete a cumulative version at each assessment point to a running measure of cumulative/average student achievement across the year.
How to Use the Tool
On the first sheet you need to enter some information about your cohort and previous grade profiles. On the first sheet enter:
- your departments historical grade profiles for the last three years.
- the expected grade profile of your current students (referred to as MEG – minimum expected grade. You may use one of many measures to set this within your institution).
- the maximum score for your assessment (this can be raw or percentage as long as it matches the scores that you enter in the left hand ‘student scores’ column).
- the student scores for the current assessment / cumulative assessments.
Paste or enter your student assessment scores into the ‘student scores’ column – don’t worry about blanks if you’re pasting from a spreadsheet – these will be ignored.
As soon as you have entered your trend profile, you can immediately get an visual overview of your historical data on the ‘Three Year Trend %‘ and ‘Three Year Trend (raw)‘ sheets. It will also show you summary statistics, in both raw and percentage form, of the current assessment such as maximum score, minimum score, average score (mean), as well as measures of dispersion such as range and SD. This is a useful summary sheet and I encourage staff to print this out (along with the boundaries sheet) for each standardised assessment and have this available in their departmental folders.
At this point, you can go to the ‘% boundaries’ and ‘Raw boundaries’ sheets and see how the scores in your current assessment compare to your three year trend and expected profile of grades. You are able to change the green cells to alter the boundaries – either by percent or by raw score depending on the sheet. As you change the grade boundaries you will see the graph update to show the new profile.
This can be used to see how the students on your current assessment, or cumulative assessments, are achieving compared to three year trends and against their expected outcome as a cohort. It can also be used to ‘award‘ grade boundaries so that they produce a spread of results that you would be comfortable with – if you are doing this though, it should not just be a case of making the current assessment ‘fit’ the shape of the expected or expected profiles.
You can download the spreadsheet below – within the zip file is a blank sheet and one with some sample data so that you can see what it can do straight away. I’d be interested in any comments, changes or additions that people make to the sheet – so if you do, please share and let me know about it.