Forced Distribution
 

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

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

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

--
(][ This Email has been scanned by Norton AntiVirus. ][)
"Mark" wrote in message
...
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

Mark wrote...
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...

I've never understood the logic behind grading on a curve. If the test
is fair, then if, say, 1/4 of students taking it score 90%, shouldn't
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, the
teacher would be fired if s/he needed to skew the scores consistently
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 of
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 at
the 90th, 60th, etc. percentiles. Scores at such thresholds would get
the lower grade. If you want them to receive the higher grade, use the
formula

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


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=274627