Spreadsheets for record-keeping and calculating final scores?

While I like technology, spreadsheets are a mystery to me. I don't know what they are and I don't know how they work. But today, as I begin the week long journey toward knowing what score each student will finally receive, it seems to me if I had a spreadsheet (as opposed to a pile of A4 sheets, one per class) and had been entering weekly records (as opposed to scratching away with a pen), then assuming I could somehow tell the spreadsheet the formula I'd use for my calculation, I might just press a button and have the scores pop out right now. They might even present as a readily printed page. Can this possibly be true?

You mean using excel? This is very doable.

Also consider using online options such as Engrade.

This is where I point out I sometimes remember Excel is spelt with one l, and that indeed, I can see it lurking malignantly down there in the Start menu, wherein it has long lain, biding its time, like a spider in a web, and from which it has rarely sprung, but I still don't know even the first thing about how to milk its poison.

While I like technology, spreadsheets are a mystery to me. I don't know what they are and I don't know how they work. But today, as I begin the week long journey toward knowing what score each student will finally receive, it seems to me if I had a spreadsheet (as opposed to a pile of A4 sheets, one per class) and had been entering weekly records (as opposed to scratching away with a pen), then assuming I could somehow tell the spreadsheet the formula I'd use for my calculation, I might just press a button and have the scores pop out right now. They might even present as a readily printed page. Can this possibly be true?

Yes.

 

Issue tangentially covered in this thread.  http://raoulschinasaloon.com/index.php?topic=9547.0

To echo KeyserSoze: Yes

Suppose that for any given assignment, I hand out letter grades in four different categories. Each letter is associated with a fixed number score and the scores in the four categories can be added together to give a single number score for the assignment. After all the assignments are completed, those number scores add up without any individual manipulation to the student's final score. However, week by week when grading assignments I want to emphasize the letter scores in the four categories rather than the single number score because the categories are different and the letter scores can be associated with value judgments, such as "satisfactory" or "excellent", and as such have more teaching meaning per assignment than a simple number score. That is, I want to hand out four categories worth of letter scores each week and after all's said and done turn them all into one number score at the end of the semester. Can a spreadsheet do that, huh, can it, huh, can it?

If I understand completely, and I might not, yes. You'll just have to associate "letter scores" or "comments" with number grades, which can be weighted and tossed into the final grade via formulae.

If I understand completely, and I might not, yes. You'll just have to associate "letter scores" or "comments" with number grades, which can be weighted and tossed into the final grade via formulae.

Yes, but it's a bit complicated if you're new to spreadsheets. In brief it requires building a separate table with the letter grades in one column and the number grade in the next column. You can record the letter grades in the students spreadsheet and use the "lookup" function in the grading formula. I use Apple's Numbers program not Excel, so not exactly sure of the Excel syntax for such a formula. the easier way would just be to record the number score in your spreadsheet using the letter grades only on the students' feedback.

I use the lookup function for another purpose. I have a table of letter grades and their ranges (e.g..A+ from 100-97, A-96-93, A-92-89, etc.) Then when I calculate and record their final number grade, the letter grade magically appears in the next column. I then have a chart which tracks the letter grades and gives me a picture of the overall spread of letter grades in the class. Very useful for me and also because my school's grading system requires me to report the spreads for each class. My spreadsheet does all the work.

BTW, I have my own grading spreadsheet which I first wrote in 1990 on Lotus 1-2-3 and subsequently used with Microsoft Works for a few years. when I got my first Mac, I switched to Numbers which also comes with a number of Education templates including a grade book with the feature I mentioned above. I just opened it and exported it to Excel thinking I might be able to send it to you, but I tested it in Excel (which I do have but never use) and a number of features in the original couldn't be handled by Excel, so no spreadsheet gift for you today.

Cool trick. I've managed to just slap together a formula for something similar to this, thanks for the heads up.

So I created an Excel worksheet. In the top left corner I have a column of letter scores and next to it a column of corresponding number scores. I discovered how to use LOOKUP. As of now if I wanted, I could create a gigantic table with student names down a side column and a row across the top that included four cells for every week and a fifth cell for the weekly calculation. In the four I'd write in each of the four scores any one assignment gets that week, and in the fifth I'd write a custom LOOKUP function that adds the number value of those scores into one final weekly score.

That is, for any given week of scores, I'd need five columns.

I'd prefer to have just one column per week. Is it possible to have one cell contain several distinct elements (at least four), and for those distinct elements to be summed later, possibly via some generic LOOKUP formula?

What I could do is generate all possible 4-letter combinations of a, b, c, d, and f, and make a very long lookup table. To make it easier, since f is always 0, I could generate all possible 4-letter combinations of a, b, c, and d and all possible 3-letter combinations of a, b, c, and d. Which is only 320 combinations all together. Right? Then, so long as I make no mistakes calculating the number values on this 2-column, 320-row lookup table, I could just enter weekly scores in cccc format (and ccc when one of the scores is f).

http://textmechanic.com/text-tools/combination-permutation-tools/combination-generator/


 

In practice, it's easy to create that lookup table.

Using that combination generator, generate all possible 4-, 3-, 2-, and 1-letter combinations of a, b, c, and d. You'll get a long list of aaaa, aaab, aaac, etc.

Then, making sure each entry will include a prefix of "=" and a delimiter of "+", generate all possible 4-, 3-, 2-, and 1-number combinations of the numbers 4, 3, 2, and 1 (IN THAT ORDER!!!! THE SAME ORDER AS THE LETTER SCORES!!!!). You'll get a long list of =4+4+4+4, =4+4+4+3, =4+4+4+2, etc.

Then, using some Word magic, create a two column table with all the letter combinations in one column (but with each combination in their own row) and the equations in the other (but likewise with each list element in its own row). Make sure you have the correct equations associated with the appropriate letter combinations. (The Word magic includes highlighting your long lists and under Insert, select Table and "Convert text to table", and other arcane stuff. Good luck.)

Then, paste the whole thing to an Excel cell. Excel does the calculations on the spot and voila, you have a 341-row lookup table.

Holy crap, you don't even need word magic. Just generate that aaaa, aaab, aaac, etc list and paste it directly to Excel, then generate the =4+4+4+4, =4+4+4+3, =4+4+4+2, etc list and paste it alongside. Kablam.

Reading through some Excel pages online, I'm getting the impression data in columns is easier to manipulate than data in rows. I'm looking in particular for ways to sum the three highest scores from a list of five or more, and there are lots of easy-ish solutions for figures in a column and, so far as I can tell, nothing for rows. Meaning, grade sheets might be easier if student names were the horizontal axis and weekly scores the vertical. Which seems like administrative insanity. How can this be so?

Scratch that. I just discovered LARGE works on horizontal ranges too.