Ranking

[mahershams]

Dear all

I would like to rank the value in column A into three groups. Top 40% as large group, bottom 40% as small group and mid 20% as middle group.

Please kindly help me. Excel file is attached here.

Regards
Shams

[amit.wilson]

Try this in C2:

=IF(PERCENTRANK($A$2:$A$137,A2)>60%,"Top",IF(PERCENTRANK($A$2:$A$137,A2)>40%,"Mid","Small"))

And then copy down to label each value as 'Top','Mid' or 'Small'.

Is that what you wanted?

Cheers

[adyteo]

Try this (add the formula in column C of your excel file):

=IF(RANK.AVG(A2,A$2:A$137)<INT(COUNT(A$2:A$137)/2.5),"Large Group",IF(RANK.AVG(A2,A$2:A$137)>COUNT(A2:A137)-INT(COUNT(A$2:A$137)/2.5),"Small Group","Middle Group"))

[mahershams]

Dear Amit and Adyteo

Thank you very much for help. When I compared the output using both formula, I have found that the results are not the same. Which one is correct? Why they are not same? Which one is more acceptable. I found some mid group of Mr Amit Formula takes place as small in Adyteo formula.

Please help?

Regards
Shams

[amit.wilson]

M - the differences are partly due to the definition of the groups.

Should 40% be part of the small group or the middle group?

If you add '=' signs in Adyteo's formula like so:

=IF(RANK.AVG(A2,A$2:A$137)<=INT(COUNT(A$2:A$137)/2.5),"Large Group",IF(RANK.AVG(A2,A$2:A$137)>=COUNT($A$2:$A$137)-INT(COUNT(A$2:A$137)/2.5),"Small Group","Middle Group"))

both the formulas will give the same result.

The differences are with only 2 values: 117012.55 and 41999.34.

Decide which group you want them in and then use the version of the formula that suits you.

Cheers

[mahershams]

Hi Amit

In my database there are 299 sample. Using your formula, I got 119 small and 119 large and rest are medium. But the other formula gives me small 173 observation and mid 7 observation. It does not seem correct to me.

Regards
Shams

[amit.wilson]

That's interesting. Try this:

in C2, run this formula:

=IF(RANK.AVG(A2,A$2:A$137)<=INT(COUNT(A$2:A$137)/2.5),"Large Group",IF(RANK.AVG(A2,A$2:A$137)>=COUNT($A$2:$A$137)-INT(COUNT(A$2:A$137)/2.5),"Small Group","Middle Group"))

In D2 run this formula:

=IF(PERCENTRANK($A$2:$A$137,A2)>60%,"Large Group",IF(PERCENTRANK($A$2:$A$137,A2)>40%,"Middle Group","Small Group"))

In E2, run this formula:

=IF(C2=D2,"","CHECK")

and copy the three all the way down.

Do you still see a lot of "CHECK" boxes?

If you do, in F2, run this formula:

=PERCENTRANK($A$2:$A$137,A2)

and copy it all the way down.

Now in column F, next to the "CHECK" values in column E, I suspect the numbers will be in either close to 40% or close to 60%. Is that correct?

More once you've checked this.

Cheers