When I teach my second-graders reading, one of the major forms of assessment is a running record: a child reads a passage to me, and I record what they got right and what they missed. For a full one, I record everything—but for a quick one, a percentage is enough. I’m wondering if Excel could help me.
What I’m imagining involves some fill-in columns: Date, Name, Book Level, and some sort of tally field. Then there are some calculated columns: word total, total correct, and percentage correct.
I want to be able to have a way that I can, very quickly, enter a 1 to indicate a correct word, and a 0 to indicate an incorrect word. Ideally, the tally field would accept a 1 or a 0, and as soon as it’s entered, it’d change the Word Total field (=previous total +1) and the Total Correct field (=If(tally field=1),Total Correct+1,Total Correct). It would then immediate reset to a blank field. Does that make sense? That way, I could keep my forefinger on the 1, my thumb on the 0, and enter results as rapidly as the student read.
If there’s an alternate way to do this rapidly, I’m all ears: the previous paragraph is just speculation on how it’s done.
You could just type in all the 1’s and 0’s as a long string into a field or cell, not worrying about clearing the field/cell constantly, then some vb code could count the 1’s once you are done keying them.
For the field to reset itself to blank, you would have to use a macro which ran every time you changed that field. And there would be no way to go back and check or change.
I would use two worksheets: an “entry” sheet, and a “summary” sheet. (Assuming you want to use this workbook to record the results as well as calculate them).
The entry sheet would like something like this:
Word
#…Correct?
1…1
2…1
3…0
4…1
5…1
Column A just allocates a number to each word in the piece.
On column B, you would use Data Validation (rather than protection) to ensure that you can only enter a 1 or a 0. You could also use Protect Sheet to prevent accidentally straying into the wrong column. While the reading is going on, you just rattle down column B with the 1s and 0s.
Your summary sheet could then use formulas to calculate the totals and total correct:
Either of these would work for the total:
= max(Entry!A:A)
= count(Entry!B:B)
And you could use a simple sum to tot up the correct words:
=sum(Entry!B:B)
After completing the assesment of that student, you would then use paste special > values to overwrite the formulae with their current values. Then you would delete the entries on the “entry” sheet ready for the next student.
I’m sure someone else has probably come up with a more elegant solution in the time it took me to type that…
(And apologies for the full stops separating the columns but I couldn’t get it work with spaces - any tips?)
Hmm…some of the solutions here require me to click two keys, I think (1 and right arrow, or 1 and enter, or 1 and tab), and this is something I’m trying to avoid. I’m afraid that with two key-clicks, I’ll be much likelier to mess up, and any error on my part can completely throw me off. Am I right in thinking that aceplace and scougs require two keystrokes per word? If there’s any way to reduce it to one keystroke, that’d be great.
raftpeople, if there’s a way to parse out the number of 1s and 0s in a field, I’d love to see it, but I have no idea how to do that. Does anyone else know? That would be an ideal solution, since if I needed to I could go back and see exactly which words were missed.
You copy it into Word, and do a find and replace for “1” and “0”, replacing each with “1[return]” and “0[return]”. The result is a column of numbers that you can copy into Excel.
It’s not Excel, but I made a little doohickey that does what you want. You can also use the buttons at the bottom if that’s easier than entering 1’s and 0’s.
Based on the user’s excel skill, I would recommend the table format suggested by SCOUGS and deal with hitting the [enter] between each number. LEN formulas are a little advanced for a reading test, not that it couldn’t work. As a person who used to have to key in a lot of numbers manually, the extra keystroke shouldn’t be an issue at all after a little practice.
If you have a numberpad just keep your pinky on [enter] and use your index finger to alternate between 1 and 0.
You could also get fancy and have a separate column for each student to see if there are questions/parts the entire class is having trouble with.
Len is pretty straight forward. It counts the characters in a string. “RRR…R.” Len 7. seven words graded in the test.
The hard part was counting the correct answers. SUBSTITUTE(H2,“R”,"") looks at a string like
“RRR…R.” and returns “…” that’s the incorrect answers. Len then knows there are 3 bad ones.
7-3 = 4 correct answers
My formula is a little long because I made it case insensitive. works for R and r.
<shrug> I tested it and it works. It can easily be modified to use 1’s if the OP really wants to.
Just make 2 sets of spinner buttons, 1 that keeps track of correct answers and 1 that keeps track of incorrect answers. Then you could just use the mouse to track the student.
ace–that looks like it’d work. Thanks! Meerkats, as I said, I know how to do it with two buttons, but you gotta trust me: although I’ve got a very fast data-entry speed, and although I use that technique for other forms of data-entry (grading spelling tests, e.g.), it won’t work here. A kid may read 2-3 words/second, meaning I need to evaluate and click buttons 2-3 times/second if I’m only clicking one per word; 2/word may mean 6 clicks/second, including evaluating, and any mistake will build on itself in errors. Slowing down is a bad option, since it breaks the kid’s flow. Darth, your idea is intriguing: can you say more about spinner buttons?
btw, I used Today() to get the date in the spreadsheet. That date will change each time the spreadsheet gets opened.
You may already know about copy values.
Just in case, to permanently date stamp your student’s test records. Setup one cell with today(). I normally do it in the Headings. Then …
Select the date cell, copy, then Paste Special to a range where you want the date stamp. A box will come up. Click Values.
There’s other ways to do this. But, this is a easy method.
aceplace, I use Today() in some places, but I confess I find it much easier to type (say) 10-20 than to cut/paste special/values. I’m not worried about the typing for anything beside the actual running record part.
Thanks–I think I’ve got a couple different but equally workable methods to try out tomorrow!
