Grading on a Curve - Formula question

[weefisheads]

I'm trying to help a friend that is a teacher and wants to make a spreadsheet that grades on a curve.

I've seached 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 -

-3.25 F
-1.5 D
-1 C
0.1 B
1.1 A

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

 

[cornflakegirl]

I may be off base, but I don't think you need the standard deviations to do this - I think you just need to rescale the scores.

Eg if you have scores between 5 and 40, and you want them to be between 10 and 95 then:

Excel Workbook
	A	B
1	Original	New score
2	5	10
3	6	12
4	9	20
5	15	34
6	22	51
7	40	95
Sheet1