Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
how do i draw a distribution chart in excel | Charts and Charting in Excel | |||
bell-shape normal distribution curve | Charts and Charting in Excel | |||
Distribution Graph | New Users to Excel |