Hi all SIFUS,

I have been in this situation for a about a month and finally need all SIFUS help on my problem.

I need to do a force ranking/ normal distribution for bell curve, which has been set as follow:

Score 90 - 100% = 5%
Score 80 - 89.9% = 5%
Score 70 - 79.9% = 5%
Score 50 - 69.9% = 70%
Score 30 - 49.9% = 5%
Score below 30% = 5%

At this moment, my data are as follow:

Score 90 - 100% = 10%
Score 80 - 89.9% = 30%
Score 70 - 79.9% = 20%
Score 50 - 69.9% = 40%
Score 30 - 49.9% = 0%
Score below 30% = 0%

How can i do a force ranking and normalised the distribution of the score based on bell curve.

Im too amateur in Excell. I do appreciate if SIFUs can give me a proper example step by step and sample of the templates.

Honestly, i already do searching in this forum, but i cannot understand the way it works.

"moskau" wrote:
How can i do a force ranking and normalised the distribution of the
score based on bell curve.
Im too amateur in Excell. I do appreciate if SIFUs can give me a proper
example step by step and sample of the templates.

Sometimes I do not see posted responses, so forgive me if this question has
been addressed adequately already.

Your lack of understanding goes far beyond inexperience with Excel. It
appears that you do not have a basic understanding of what it means to grade
on a curve or otherwise force a particular distribution of scores.


"moskau" wrote:
I need to do a force ranking/ normal distribution for bell curve,
which has been set as follow:
Score 90 - 100% = 5%
Score 80 - 89.9% = 5%
Score 70 - 79.9% = 5%
Score 50 - 69.9% = 70%
Score 30 - 49.9% = 5%
Score below 30% = 5%
At this moment, my data are as follow:
Score 90 - 100% = 10%
Score 80 - 89.9% = 30%
Score 70 - 79.9% = 20%
Score 50 - 69.9% = 40%
Score 30 - 49.9% = 0%
Score below 30% = 0%

First, you cannot change scores. Forcing a particular distribution means
assigning specific percentages of scores to __categories__. For your
example, you might want to distribute the scores to the grades A+, A, B, C,
D and F.

Second, there is nothing "bell-shaped" about the distribution that you
require (5%, 5%, 5%, 70%, 5%, 5%). Even if it were, it probably would not
fit a "normal distribution". But it is a common misunderstanding that
"bell-shaped" and "normal distribution" are synonyms. Most people mean the
former (bell-shaped), not the latter; and even that term is debatable. They
usually mean an arbitrary distribution that is symmetrical.

Finally, there is a mistake in your required distribution: it does not sum
to 100%. Perhaps a 5% category is missing on the low end; or perhaps the
second-lowest category should be 10%. I will go with the latter, even
though that is not "bell-shaped" by any definition.

Download the example Excel file "grade on curve.xls" from
https://www.box.com/s/4ru8ragc4wejtsp42clv.

Generally, we can use PERCENTILE to force an arbitrary distribution.

Start by listing the scores in column A (A1:A100). Then create the
following table. Note that the table is ordered with the __lowest__
category (F) first. This is necessary to allow us to use the FREQUENCY
function as demonstrated below.

E1: =ROUNDDOWN(PERCENTILE(A1:A100,5%),1)
E2: =ROUNDDOWN(PERCENTILE(A1:A100,15%),1)
E3: =ROUNDDOWN(PERCENTILE(A1:A100,85%),1)
E4: =ROUNDDOWN(PERCENTILE(A1:A100,90%),1)
E5: =ROUNDDOWN(PERCENTILE(A1:A100,95%),1)
E6: =MAX(A1:A100)

D1: =MIN(A1:A100)
D2: =ROUND(E1+0.1,1)
copy D2 into D3:D6

The percentile is based on the __cumulative__ percentage of scores to be
included in the category. The lowest category contains 5%. The
second-lowest category contains 10%; that is, 15% less the lowest 5%.

To check the distribution, select F1:F6 and array-enter the following
formula (press ctrl+shift+Enter instead of just Enter):

=FREQUENCY(A1:A100,E1:E6)/COUNT(A1:A100)

formatted as Percentage.

If you do not see __exactly__ the distribution you require, the problem
might be "quantization error": discrepancies (not real errors) that arise
due to rounding and counting integers.

However, the problem might be the way that Excel PERCENTILE behaves and/or
my use of ROUNDDOWN. You might try fudging some of the boundaries in column
E either manually or by changing ROUNDDOWN to ROUND in some or all
instances.