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:

Please Login or Register  to view this content.

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):

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

In D2 run this formula:

Please Login or Register  to view this content.

In E2, run this formula:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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