I'm trying to help a friend that is a teacher and wants to make a spreadsheet that grades on a curve.
and found the following -
assuming Grades are in A10:A20, then (A10-AVERAGE(A$10:A$20))/STDEV(A$10:A$20) gives
the standard deviation of a particular score.
Also, I found something that said a distribution for grades would
look something like this -
THEREFORE, =VLOOKUP((A10-AVERAGE(A$10:A$20))/STDEV(A$10:A$20), $X$3:$Y$7,2) yields a grade for the score in
A10. Using this, I get pretty much the distribution I want - 10-15% A's, 30-40% B's, 30-40% C's, 10-15% D's and F's.
My challenge is to convert the StdDev into actual Scores, not just grades. At the end of the year, it's hard to
calculate 10 A's, 5 B's, 1 C, so it would be great to assign an actual score BASED ON A CURVE. Essentially, for each quiz,
the score they get might be a 40, but based on a curve their score might actually be a 95 or whatever since the 40 was the
highest score. I could assign an F a 50, a D a 60, a C a 70, etc, but I'm curious if there is a way to assign a score to
every number between 1 and 100. So -4 StdDev is a 1, a -3.95 is a 2, all the way to anything greater than 1.5 is an A+.
Is there a formula that could do this or even a lookup table that would have the right increments? Or am I
completely off or totally bonkers.
I'd appreciate any suggestions