Physics & Physical Science Demos, Labs, & Projects for High School Teachers

My Gradebook and Curving Algorithm

Posted on: November 15, 2009

normal_curveHere’s a little end-of-quarter gift for you.  I’ve been meaning to post my excel gradebook and curving routine for some time.  I think some of you are going to want to send me a Christmas present for this one.  (And if you are so inclined, I’ll give you an address to mail it to.)

The gradebook isn’t really all that special, it just does exactly what I need it to do.  The second sheet has a few sample grades so you can see how I use it.  In a nut shell, here’s the idea:

  • A line for each kid, you can do first & last names, I tend to do last, first in case I need to sort.
  • Along the top is the graded assignment.  I often use a comment in the field so I know what the actual homework was for that grade.  It’s helpful when they were out and need the assignments.
  • The column marked “Point Adjustment” allows me to exempt the grade for someone that was out.  You can see the example on sheet 2.
  • I have the kids give me a code, like their birthday or address.  Then when I go to print, I hide the name, sort by the average grade (decending), hit print, then undo, undo.  Don’t sort the first column.
  • You will see some boxes at the bottom, two stacked and ####’ed out.  That is the average and std dev.  When I give a test or quiz, I copy and paste one of those to the bottom.  It lets me know how the trend is going.
  • If a kid is failing or near failing, I color their line red.
  • You can keep extra rows, but make sure the averaging function is removed or it will include those zeroes into the class average.
  • A missed assignment can be blank, it still counts as a zero unless you do a point adjustment.  I normally don’t take late work, so I just put in a zero for those, but it’s useful for kids that were out to see a blank. It reminds them to turn in the work that was due.
  • When the quarter is over, I make a copy of the spreadsheet and store it under admin archives.  Then go back to my original and wipe out all the grades and start over.

What’s great about this program is I can make changes quickly.  I used to keep this on my USB drive, but now it sits in DropBox so I have access to it from everywhere.  I print grades at least once a week, it’s very quick to post grades on my bulletin board, and the kids keep me honest if I mess up their grades – which in the end helps both of us.  It’s much faster than our school grade book program, so I consider my spreadsheet the master if there is a conflict.

Now, the curve routine.   After a test that will need curving (and don’t they all), I copy and paste just the grades to the sheet labeled ‘stuff.’  Once there, I sort them high to low and then use the Average function and the Standard Deviation function.  I really only want the average, but the std dev gives me an idea how spread out the grade pattern looks.  I’m in a bullet kind of mood…

  • Look at the grades and the average.  I like my test averages to be 75-77, but they are usually like 60.
  • Go to the Curve sheet.  Go to the yellow boxes.  Here is where you get to play a little.  Take the highest test grade and make it what you want it to be.  If someone stood out from the rest, I may make it 100.  If not, 95 works for me.  Now the low – I usually start out with the low grade being curved to a 50.
  • Go down to the single yellow box and enter the average grade for the test.  Below it, you can see what the average grade curves to.
  • If the average is below what you want, you can boost the curves-to grade for the high score or the curves-to grade for the low score.
  • When you have it where you want it, change the title on the Curve sheet and hit print.  It will print the curve, plus what the high, low, and average curve to.
  • To enter into the grade book, I usually make a new column called ‘curve’ worth no points and put the delta in there.
  • Pointer (worth and extra dollar or two) – use the comment function and put the curve numbers onto the test header.  You can see this on the period 3 sheet on the test dated 11/10.  This way, if I have to go back and recreate the curve, you have the data to set it up instantly.

What I’ve found is that by keeping my test average around a C/C+, I have just the right grade distribution at report card time.

The file is in my public folder on DropBox:

If you have a problem with DropBox just email me and I’ll send you the file directly.


1 Response to "My Gradebook and Curving Algorithm"

You could always host the file at dropbox and put it in your Public folder. Then, you can just right click on it and get the link for it. It’s pretty nice.

What do you think? Your opinion matters, leave a reply.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

What’s New in 2013/2014?

Every year brings a change, this one is no exception.

I will be picking up the sophomore honors Algebra II class to keep them separate from the juniors. This should help accelerate them and put them on a stronger track towards Calculus. Looks like there will be only one section each of Physics and Calculus, but still two of Robotics & Engineering.

Hot topics this year are going to be the Common-Core Standards, Standards-Based Grading (SBG), improving AP Calculus scores, and somehow adding Python, maybe as a club.

Polls in the sidebar

Just a quick poll to help me understand who is stopping by my blog.

Yeah sure, lots from America, but look who else is here…

If you are badly in need of more email or for some reason jonesing for a physics fix, enter your email address so I can bother you with my newest rant on science.

Join 314 other followers

Blog Stats

  • 1,299,589 hits by nerds like me since June 1, 2008
November 2009
« Oct   Dec »
%d bloggers like this: