Forum Discussion
Tracking student progress
I'm looking to create my own grade book to track student progress over time. My school district refuses to provide an adequate grade book solution for the competency-based assessment model which we now use. My frustrations have led me to the decision to just create my own. I can create a spreadsheet that lists students, shows each assignment, which competencies it addresses, and student performance with respect to each competency. What I'm looking for is a way to bring together all the scores for each competency. There are over a dozen competencies to address at grade level and each assignment will be focused on just a small number of competencies. Over the course of, say, 10 assignments, competency #1 will only be assessed in five of those, and others will come up more frequently and others less often. I want to be able to see that Jimmy has struggled initially with only a 1 on the first three instances this competency was assessed but on the fourth, he scores a 2, and on the fifth, he scores a three. It will vary how often each competency will come up over the course of those 10 assignments.
So my question is what formulas do I need to learn about in order to accomplish this? I'm currently looking into Pivot tables. But, I'd like the have the performance colour coded, red, yellow, green blue, instead of a value or a score.
JeffreyB593 Not sure I follow, but when I select/enter some new Competencies and refresh the pivot table, all seem to be included properly. With respect to adding new assignments, select the last block from the table (i.e. T1:W37). Copy and Paste to X1. The table is expanded with all the formats and data validation in place. Do NOT select entire columns T to W. Then it will not work.
I appreciate the frustration you're experiencing. Your description, sadly, doesn't go far enough for a layman or outsider to really give any specifics. I wonder if you could share either links to whatever guidance IS available for tracking--even if it's on paper ledger sheets--the development of these competencies. What DO they make available?
Lacking any of that, the more detailed you could be in your description of how specifically each competency (that applies) IS scored or graded in each assignment. Does a student's performance on each assignment get assessed not only as a whole (e.g., the old-fashioned letter grade) or is it assessed on a 1-NN basis for each of the applicable competencies, OR both?
Etc.
I'm less worried (and so should you be) at this stage of design about the formulas or functions you might need, more about such things as:
- what data are collected?
- how best to store that data? (what kind of database layout)
- how granular that data needs to be?
Then there are the questions of what form the "Output" should take?
- What is the range of results that can be expected?
- What meanings would be derived from those results?
- What would be ways to display the results that would be most conducive to making those meanings clear?
Excel has remarkable abilities to analyze, summarize, process raw data that is well organized in a tabular form. You've already mentioned the Pivot Table, and that for sure is one of those tools...... But before we get to that and other methods, explain the big picture a bit more fully please.
- Mathetes,
Thanks for your reply. Assessing students with points and percentages is out of fashion now. Assessment is based on proficiency. There are skills that student are to demonstrate their proficiency. For Grade 10 Composition for example, some of the competencies look like this:
*Respectfully exchange ideas and viewpoints from diverse perspectives to build shared understanding and extend thinking
*Respond to text in personal, creative, and critical ways
*Assess and refine texts to improve clarity and impact
*Demonstrate speaking and listening skills in a variety of formal and informal contexts for a range of purposes
*Use writing and design processes to plan, develop, and create engaging and meaningful texts for a variety of purposes and audiences
*Express and support an opinion with evidence
*Use the conventions of Canadian spelling, grammar, and punctuation proficiently and as appropriate to the context
*Use acknowledgements and citations to recognize intellectual property rights
*Transform ideas and information to create original texts
There are four levels of proficiency: Approaching, Developing, Proficient, and Extending. Any tasks assigned provide students with opportunities to display their proficiency in the above core competencies. As a teacher, you are trying to get kids to proficient.
So I'm trying to work out how to create a spread sheet that will students down the first column and then assignments and the 2-4 core competencies that are being assessed through that assignment. What ends up is a spreadsheet with some nested column headings. Now, I have been watching some videos and playing with pivot tables to see see what I can come up with. So far I have created a spreadsheet that looks like what I want, and then used unpivot to gather that all into a proper table and then creating a pivot table from that. So far, so good. The tables that results looks good but seems limited to counting, averaging or adding the data, which, of course, isn't in the form of a number. Ideally, I would have each of the levels coded to a colour. I wish I could show you what I have created so far.
Things do not need to get too granular. I can just store this as a excel file. The results need to show me, the student, their parents the student's growth. As a whole class, I be able to see what is working and what is not. I'll be able to see how often each of the core competencies are being assessed.- You can show me what you've created so far. Use the ability to send me a personal message here in this forum. Click on my user name and you'll be taken to a page where in the top right corner you'll see a way to Message me. There, to that message, you will be able to attach a copy of the spreadsheet you've created. Or post it in OneDrive or GoogleDrive or one of the other cloud services and post a link here.
Cross-posted here:
https://www.myonlinetraininghub.com/excel-forum/excel/tracking-student-competencies-over-time
Attaching the file (yours with my revisions) I uploaded there.
