Norm.Dist on Test Scores for a Curve

1. Norm.Dist on Test Scores for a Curve

Hello and thank you in advance for your help.

I would like to know the most fair curve possible to give on a test. I want to adjust each individual score in order to make it more "normal" (I don't say normalize because I think that may have a mathematical connotation that doesn't match what I want).

In the attachment, I have on the left some random scores and then the norm.dist function. On the right is the normal distribution. In the middle are two charts: the top is the real scores and the bottom is the normal distribution.

What I want to know is how to adjust each individual score in order to make it more like the normal distribution. I want those at the bottom to get a better curve than those at the very top. Is there some kind of function (or philosophy) I can apply here?

Again, thank you for your help.

2. Re: Norm.Dist on Test Scores for a Curve

One way:

 Row\Col A B C D E 1 Percentile Cumu Grade 2 10% 0% F A2:A6: Input 3 10% 10% D B2 and down: =N(B1)+N(A1) 4 60% 20% C A4:A8: Input 5 10% 80% B 6 10% 90% A 7 8 Score Rank Grade 9 48 0.0% F B9 and down: =PERCENTRANK(\$A\$9:\$A\$71, A9, 4) 10 60 1.6% F C9 and down: =LOOKUP(B9, \$B\$2:\$C\$6) 11 65 3.2% F 12 69 4.8% F 13 70 6.5% F 14 73 8.1% F 15 74 9.7% F 16 74 9.7% F 17 74 9.7% F 18 75 14.5% D 19 75 14.5% D 20 76 17.7% D 21 76 17.7% D 22 78 21.0% C 23 79 22.6% C 24 79 22.6% C 25 79 22.6% C 26 80 27.4% C 27 80 27.4% C 28 80 27.4% C 29 81 32.3% C 30 81 32.3% C 31 81 32.3% C 32 83 37.1% C 33 83 37.1% C 34 83 37.1% C 35 83 37.1% C 36 83 37.1% C 37 84 45.2% C 38 84 45.2% C 39 85 48.4% C 40 85 48.4% C 41 85 48.4% C 42 85 48.4% C 43 85 48.4% C 44 85 48.4% C 45 85 48.4% C 46 86 59.7% C 47 86 59.7% C 48 88 62.9% C 49 88 62.9% C 50 88 62.9% C 51 88 62.9% C 52 88 62.9% C 53 89 71.0% C 54 89 71.0% C 55 89 71.0% C 56 90 75.8% C 57 90 75.8% C 58 91 79.0% C 59 91 79.0% C 60 91 79.0% C 61 93 83.9% B 62 93 83.9% B 63 93 83.9% B 64 94 88.7% B 65 94 88.7% B 66 95 91.9% A 67 95 91.9% A 68 97 95.2% A 69 98 96.8% A 70 98 96.8% A 71 98 96.8% A

3. Re: Norm.Dist on Test Scores for a Curve

Hello,

Thank you for your reply! What exactly is happening here? It's hard for me to parse the functions.

Thanks

4. Re: Norm.Dist on Test Scores for a Curve

You decide what percentage of students should get each grade based on their rank (A2:A6), and everything else is calculated.

There are seveal other techniques described at http://divisbyzero.com/2008/12/22/ho...assign-grades/

5. Re: Norm.Dist on Test Scores for a Curve

Oh, okay. So I could add an if( function. Like, =if(rank>90,score+1,if(and(score>80, score<=89),score+5,if(and(score>70, score<=79),score+7,if(and(score>40, score<=69),score+10))))

Does this make the scores more normal, or just better and fairer for those at the very bottom? That is, if I apply that function, will the scores look more like a normal distribution?

6. Re: Norm.Dist on Test Scores for a Curve

I don't understand what your IF function is supposed to do.

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1