Tracking student progress

[JeffreyB593]

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.  

[#2]

@JeffreyB593 

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.

[#3]

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.

[#4]

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.

[#5]

@JeffreyB593 

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.

[#6]

@JeffreyB593 

First, let me suggest that you keep us informed of the cross-posting you're doing. I wasn't even aware of that other forum where you and @Riny_van_Eekelen have interacted some. Unless I'm mistaken there is more action here than there. But it's good to know of that other resource.

Second, I've attached my first attempt at the first step of a solution to your need, which amounts to a revision in the way you organize your "raw data," my suggestion being a clean database in which each student gets whatever the competency rating (if that's not the correct terminology, please excuse this octogenarian) is for the relevant competencies, and there's a date reflecting the completion of the task or assignment.  (See the new tab, all the way to the right)

Excel works very well with basic (well-thought-out) tables of data....there are numerous tools for summarizing and reporting on that data, Pivot Tables being one of the most powerful and popular, but not the only.

This contains a more extensive description of what I have in mind......

By the way, hold off on colors for the time being. Use a number or letter for rating (again, please excuse if that's not the right way to say it). Colors can come along via Conditional Formatting later in the process.

[#7]

@mathetes 

Thanks to all that have pitched in with suggestions and even some changes. The semester has started and there hasn't been much time to come back to the forum but I have been tinkering and I have something that I think will work for me. I would like to have the Competency row to be a pull down menu but each time I try it the pivot tables don't seem to refresh along with it. I'm certain that it is due to the change from a number to text. I was trying to find a way to distinguish between the two sets of outcomes. Ideally, I would love to have the whole explanation to be a part of the pull down and only the code to be the value that is inserted. But I expect that is asking for the impossible. The other matter is the adding of new assignments. I would love to add it as a block. Right now I am just selecting the four blank columns and adding them to the left.

[#8 · Accepted solution]

@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.

[#9]

@JeffreyB593 

I"m going to keep following this thread for the sake of curiosity, but leave it to @Riny_van_Eekelen to be making suggestions on how the spreadsheet could work most effectively.

I will offer this one observation on the whole grading structure: it is (to me) reminiscent of the way tennis players can be rated. I don't know, Jeffrey, if you've ever played tennis to the extent that you've been assigned a number meant to reflect your "skill level" but it IS a useful way to get yourself into matches that are competitive, neither too easy (boring), nor too hard (utterly frustrating).

Here's an image of the numerical ratings and their descriptions of the most popular system.

I was a decent tennis player; I had at one time a solid 3.5-4.0 rating, played with my fellow club members in inter-club matches.  The thing that is worth realizing about ratings like these--and I would submit, like yours as well (and you're probably well aware of this)--is that there's a LOT of overlap between adjacent ratings. A 3.5 player can sporadically do some of what a 4.5 player does regularly. The other thing that really is true is that from a learning perspective, the truly meaningful differences are found between 3.0 and 5.0. Below that range, one is needing to learn everything, the ratings are somewhat meaningful, but not worth spending a lot of time on. Above that range, it's really the smallest differences--more a matter of consistency in playing, shot after shot, game after game, match after match--that differentiates a Roger Federer from the person ranked 100th in the world, or even a state champion. That is to say, above 5.0, it's not skills or competencies that need to be learned; it's practice, practice, practice, play, play, play.

The reason I bring all this up--and I will certainly defer to your expertise in your field--is that to my layman's eyes, your fine-tuning of the differences at each competency level do bring up specific competencies that are worth addressing, for sure, but by not only assigning a numeric rating to each point on the scale scale and then seeking to further refine those with differentiating "Emerging" from "Developing" from "Proficient" and "Extending" you're making overly complicated the act of giving what could be--delivered in the form of speech--useful feedback to the student. 

For example, I can imagine a student puzzling (uselessly) over "Why was I only 'Proficient' in competency 'RLV.6'?"

Now, maybe I've totally misunderstood the system. However, having been through multiple years of graduate school, having had a career in helping managers in the corporate world recognize their blind spots when it comes to prejudices, I'd rather see a less structured grading system (not un-structured, just less-structured) with more emphasis on one-on-one coaching feedback on the very same competencies but delivered face-to-face.   And I do realize, that may be happening away from the spreadsheet; it's just that I'd tame down the spreadsheet.

'nuff said. Feel free to totally ignore.

[#10]

@Riny_van_Eekelen 

Here is the version where I change the Competency row to a pull down menu with the codes that I created on Sheet 4. I'm guessing I need a Concatenatex formula to add to the pivot table similar to the Assessment formula but I'm still learning that one. 

[#11]

Thanks so much. The old system, a six-point scale, was much easier to work with and I think the students understood it better, but these new competencies and the scale are now Provincially mandated by the Ministry of Education. Some folks who are not teachers sat around a boardroom table and thought this up. Now I'm required to use it. As you said, daily conversations with students allow them to know where they stand but I have to report out on all of these. If I was closer to retirement I wouldn't bother but I still have eight years to go so I can't really buck the system. All of this wouldn't have been a problem if they had provided some sort of reporting software when they changed the Assessment model. I could rant for days on this topic so I'll stop.

[#12]

@JeffreyB593 

The old system, a six-point scale, was much easier to work with and I think the students understood it better, but these new competencies and the scale are now Provincially mandated by the Ministry of Education. Some folks who are not teachers sat around a boardroom table and thought this up. Now I'm required to use it.

Yet another example of "best intentions having unintended side effects" downstream, decisions made by folks who don't actually have to live with the consequences!!

If I was closer to retirement I wouldn't bother but I still have eight years to go so I can't really buck the system. ... I could rant for days on this topic so I'll stop.

If I were a taxpaying voter there, I'd be ranting on it myself. 

I hope your last eight years go well despite this kind of interference!

[#13]

I think I figured it out. Learned more about Concatenatex and it wouldn't be helpful. Went into the Query and was able to change the column from Whole Number to Text and it works!

[#14]

I figured it out. Thanks for all your help and getting me pointed in the right direction.