Ranking numbers 1st to 5th

[CDandVinyl]

In the range C3:C300 , I have a list of numbers resultant from formulas.

I'd like in D3:D300 to rank the top 5 numbers in C by 1st , 2nd , 3rd, 4th and 5th. Other numbers in C outside this ranking , or at zero can show blank in D.

Can someone suggest a formula for column D to satisfy this?

Grateful for any advice.

[TMS]

If you have the functions in Excel 2003, try:

Please Login or Register  to view this content.

possibly Array Entered.

[CDandVinyl]

OK thanks TMS for this. It's doing the job and finding the top 5 numbers in C , but is copying the number itself from C over to D , rather than saying "1st" , "2nd" , "3rd" , "4th" or "5th" in D. Sorry if my original post wasn't clear on that.

Is it possible to adapt it to do this?

Grateful for your help.

[TMS]

Maybe:

Please Login or Register  to view this content.

Or, if you must:

Please Login or Register  to view this content.

[TMS]

And … You're welcome. Thanks for the rep.

[CDandVinyl]

Thanks again. I'm grateful for your time on this.

I ran the second formula and curiously it's counting five numbers from row C an no more than that. So , where there are numbers in equal position , it's allocating a position at the expense of others in the list.

For example

I'd expect this :

C D

18 1st
18 1st
17 2nd
16 3rd
15 4th
15 4th
10 5th
10 5th


But it's giving :

18 1st
18 1st
17 3rd
16 4th
15 5th
15 5th
10
10

with no '2nd' place , and no rank for the two 10's.

I'm afraid the logic is beyond my capabilities (!) , but maybe you can see the way through..

[TMS]

I don't have your data. I just used RANDBETWEEN to generate some data. But, what the formula is doing is what I would expect it to do. That is, if you have two equal highest values, they would be first equal, or joint first place. The next highest value would be third, not second, as you already have two values higher than it. The next highest, fourth.

[TMS]

The RANK function does the same thing:

Please Login or Register  to view this content.

Probably should have used that in the first place.

[jindon]

D3:

=CHOOSE(ROUND(MIN(SUMPRODUCT(($C$3:$C$300>=$C3)/COUNTIF($C$3:$C$300,$C$3:$C$300)),6),0),"1st","2nd","3rd","4th","5th","")

filldown

[TMS]

You CAN do it with a Helper Column, say, Column D. Use

Please Login or Register  to view this content.

copied down. Then Rank the Helper Column.

Please Login or Register  to view this content.

copied down.

[CDandVinyl]

Many thanks guys. This is working perfectly. I appreciate your time and expertise. Problem solved!

[TMS]

You're welcome.

Which solution worked for you?



Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[CDandVinyl]

Hi TMS - Well they both work fine , although the processing time is more noticeable in one than the other. I was also toying with the Rank function and tried this :

Please Login or Register  to view this content.

This limits the ranking to the top five results , which is good , but brings up the '1st equal' issue again and jumps ranks. I do understand why that should be even though it's not what I had in mind. I don't know if RANK can be modified not to jump when there are equal rankings.

Nevertheless with the above in place , I'm wondering if some event code could be employed to change the result of the Rank formula in D from "1" to "1st" , "2" to "2nd" and so on to 5. This might avoid a helper column if it can be done.

[TMS]

Why not just go with the Helper Column as described in post #10?

That's the only one that works the way you want it to. See the comparison also in post #10.

Value    Helper    Large     Large     Large     Rank    Rank    Rank    With Helper    jindon
296    296    296    5    5th    296    5    5th    5th    3
295    295                                4
297    297    297    3    3rd    297    3    3rd    3rd    2
300    300    300    1    1st    300    1    1st    1st    1
294    294                                5
300    300    300    1    1st    300    1    1st    2nd    1
297    297    297    3    3rd    297    3    3rd    4th    2 

[jindon]

CDandVinyl,

vba, if you like

Please Login or Register  to view this content.

[CDandVinyl]

TMS - Yes , I find the helper column solution works really well. I was just being curious about the RANK version I mentioned and using event code to change 1 to 1st and so on..

Thanks Jindon - this solves it too , but is changing the data in C rather than D. I'll try again.

[TMS]

As far as I am aware, the RANK functions works as we have seen. End of.

I have looked for alternative approaches and found this:

Please Login or Register  to view this content.

However, this just produces the same rankings as jindon's formula. So, like jindon’s formula, it doesn't jump rankings, but it does duplicate them.

This one DOES seem to work as you want it to:

Please Login or Register  to view this content.

It produces the same results as using the Helper Column. Yayyyy!

Solutions courtesy of: https://www.exceldemy.com/rank-excel...th-duplicates/

See also: https://www.exceldemy.com/rank-with-ties-in-excel/

[CDandVinyl]

Very clever formula. I'm not sure I'm up to following the logic , but as you say it does do the job.

Looks like RANK can't accommodate 1st equal , 2nd equal , 3rd equal and so on without skipping ranks , as shown in post 6.

Thanks again.

[TMS]

Looks like RANK can't accommodate 1st equal , 2nd equal , 3rd equal and so on without skipping ranks , as shown in post 6.

No, not on its own. It needs some fine adjustment (with a small hammer )

[TMS]

Note: the second link explains the logic.

[CDandVinyl]

Yes , I did have a good look at the link examples. Some interesting variations on the theme about breaking ties.

[wk9128]

Formula
Cell M3 formula , Drag down

Please Login or Register  to view this content.

VBA CODE
Cell N3 formula , Drag down

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[CDandVinyl]

Hi wk9128. Thanks for this. I've applied the formula to M and N , but I'm not sure how or where you intend the VBA to be applied. Perhaps you could advise..

[wk9128]

You're Welcome. Glad to help . Thank You for the feedback.

- you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help
was given. By doing so you can add to the reputation(s) of those who helped.