Thursday, May 3, 2012

Time-saving formulas

Several semesters ago I made the switch from the tedious work of calculating grades by hand to the semi-tedious work of using a spreadsheet. As for online grading systems, I just wasn’t made for these times: I often assign very short pieces of writing as seems appropriate (playing it by ear, as my dad would say) and thus cannot devise a grading system in which every piece of work fits into some pre-established scheme of percentages. So I have no answer the eternal question “How many points is this worth?” Every piece of writing counts toward the percentage of the semester grade that goes to writing, with longer work and the best work counting for more.

The formula above will save me perhaps an hour of calculating grades for one class. D1 is the average of several very short pieces of in-class writing. E1, F1, G1, and H1 are three-page essays. I1 is the best of those three-page grades, counted as an extra page, as it were. These grades, added together, get divided by the total number of pages: /14. Multiply the result by six, add participation times two, the final exam times two, divide by ten, and there’s the semester grade.

Humanists: if you’re still doing grades by hand, you might want to look into creating a spreadsheet. It’s always good to learn new skills, and the savings in time can be considerable. The one caution I’d offer: proofread car fully! A single mistyped cell name = disaster.

May 4: In the comments, a much easier way to create a formula to do the work of the one above, courtesy of The Arthurian.

A related post, sort of

[Yes, proofread car fully.]

comments: 6

Elaine said...

Now I have a headache.

The Arthurian said...

Hi, Michael. I used to distrust and dislike spreadsheets (because there are formulas everywhere! that are easy to mess up) but I like 'em now.

Just to be sure you know, you can do it this way:

1. Go to the cell where you want the result of your formula.

2. Hit the EQUAL sign, then use the mouse to click cell D1, then type +3*sum(, then click-and-drag across E1, F1, G1, and H1. And then type )+I1 and hit ENTER.

You can also hit the F2 key to edit your formula: add the open-paren after the equal sign, the close-paren at the end, and the 'divided by 14' to make it useful. (Press ENTER when done editing.)

Also, you can copy your completed formula down to other rows, row 2 and row 3 and like that. So you don't have to type the formula over again for every row.

(Columns in a spreadsheet go up-and-down, like the columns in a building. Rows go the other way.)

The result I get looks like this:


Where I get lost is in the new Excel "ribbon" that lays waste my, dunno, twenty years of familiarity with the old FILE... EDIT... INSERT... VIEW... menu. The ribbon seems to have permanently disabled me.

Michael Leddy said...

That’s a much more elegant formula than mine. I use the Mac app Numbers — I’ll see if I can do what you describe. Thanks for the careful explanation.

Michael Leddy said...

P.S.: Elaine, you’d have a bigger headache calculating by hand but less of a headache using the Arthurian formula. :)

Michael Leddy said...

Arthurian, your instructions work perfectly in Numbers. I just had to add a “(” before D1. Your way is so much easier than what I was doing. I’m glad I posted my formula and so found a better one. Thank you.

The Arthurian said...

Excellent! Glad it worked. I did have to be very car ful, typing it all in.

Funny, it never occurred to me that you might not be using Excel.