+ Reply to Thread
Results 1 to 6 of 6

Norm.Dist on Test Scores for a Curve

  1. #1
    Registered User
    Join Date
    12-09-2011
    Location
    Dallas
    MS-Off Ver
    Excel 2003
    Posts
    6

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    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
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-09-2011
    Location
    Dallas
    MS-Off Ver
    Excel 2003
    Posts
    6

    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. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    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. #5
    Registered User
    Join Date
    12-09-2011
    Location
    Dallas
    MS-Off Ver
    Excel 2003
    Posts
    6

    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. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Norm.Dist on Test Scores for a Curve

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calling Application.NORM.S.DIST issue???
    By Boo123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2014, 12:15 PM
  2. scores from test
    By Lene in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 08-18-2012, 09:33 AM
  3. Replies: 1
    Last Post: 03-21-2012, 11:22 AM
  4. Pull sub-test scores from test w/best overall test score (PSAT)
    By PowerSchoolDude in forum Excel General
    Replies: 0
    Last Post: 11-19-2009, 08:29 PM
  5. Calculate mean of test scores from rows of test answers
    By RiotLoadTime in forum Excel General
    Replies: 1
    Last Post: 07-26-2006, 12:20 PM

Bookmarks

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