Forum Discussion
Tracking student progress
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.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.
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.
- 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.
