Forced Distribution

[Guest]

Is there a mechanized way to take a series of grades and bump them up or down
to fit a forced distribution, eg. no more than 10% get A's, 30% B's etc...

Thanks,

Mark

 

[sulprobil]

Take my macro:
http://www.sulprobil.com/html/redw.html

Write in cell A1:
=INDEX({"A","B","C","D"},INT(redw(10%,20%,30%,40%)*4)+1)

Copy A1 down to A20, for example. On average (!) you will
receive in this example 10% grade A, 20% grade B, etc.

If you want numbers to change each time you hit F9, place
the command Application.Volatile at beginning of that
macro.

HTH,
sulprobil

 

[OZDOC1050]

Could you not just rank them by score then number them ( the students ) 1 -
200 ( for example ) then from that assign the grades

to do this I would place my parameters into a set section say top left

200 (number of students)

a = 10 ( variable ) formula = 200 ( students ) / 100 * a ( = 20 )
b = 20 ( """"" ) formula = 200 ( students ) / 100 * b + a ( = 60 ) and so
on down
c = 30 ( """"" )
d = 40 ( """""" )

the I would write a macro ( record it ) that ranks the students top to
bottom then a loop macro or similar that would number down the side
lastly an if macro that referenced the variable cells.

if number < = 20 , a, if number > 20 and <= 60 ,b and so on

this would loop down until done

by changing the variables you can tweak it until your happy.

hope it helps
R
Pete

 

[hgrove]

Mark wrote...

Is there a mechanized way to take a series of grades and bump them u or
down to fit a forced distribution, eg. no more than 10% get A's, 30

B's etc...

I've never understood the logic behind grading on a curve. If the tes
is fair, then if, say, 1/4 of students taking it score > 90%, shouldn'
all of them get As? Likewise, if more than 1/2 score below 50%
shouldn't those fail?

Now if there were some methaphysical fairness involved, to wit, th
teacher would be fired if s/he needed to skew the scores consistentl
up or down, then curves would be OK.

As a pure thought experiment, if the top 10% of scores should get As
then with the named range Scores containing the scores and x one o
those scores, the grade for x would be given by

=LOOKUP(PERCENTRANK(Scores,x),{0;0.3;0.6;0.9},{"D";"C";"B";"A"})

This won't give precise cutoffs if there are any ties for the scores a
the 90th, 60th, etc. percentiles. Scores at such thresholds would ge
the lower grade. If you want them to receive the higher grade, use th
formula

=LOOKUP(COUNTIF(Scores,"<="&x)/COUNT(Scores),{0;0.3;0.6;0.9},
{"D";"C";"B";"A"}