[Solved] RANK Function

[Wheelz]

Hi,

In the attached file you will see a sample record of competition scores with the RANK listed in column B.
I'm wanting to reference this rank value to another sheet to return the "Top 10".
My problem is that in the event of there being any "tied scores" the rank value is duplicated and the following value omitted. eg. 1,2,2,4,4,6 etc
I'm unable to "SORT" the records to bring the Top 10 to the top as this will break formulae in other sheets.
Is there a way to automatically assign a unique value to the top 10 which will include the tied scores?
Alternatively, return the rows containing the ten highest scores?

Many thanks,
Pete

[Lupp]

You need to disambiguate the ranking. You can do so in different ways. One of them using the rownumbers for a tiny but significant numeric modification is demonstrated in the attachment.

1. Do not modify the original values if you need to refer to them in any additoonal expression. Use a helper column instead.

2. "Stable" means that any subset of (unmodified) equally ranking rows keeps the internal order after disambiguation.
2. To get it stable the numeric modifications must be negative in case of descending ranking. Change sign for either
2. ascending ranking or "antistable" order.

[Wheelz]

Hi Lupp,

Thank you so much for that.
I guess that's called thinking outside the box.
I was thinking of employing a second column to interpret the rank but couldn't think of how. I would never have come up with your method.
Even with the expected 150 or so rows it makes little difference to the rounded value. If entries extended too high I guess you'd simply /10000000 instead.
Much appreciated.
Pete

[Lupp]

Due to my limited understanding and my poor English, I am in trouble if it comes to a complete explanation of the circumstances and conditions ...

What was within reach for me is included with the new reworked example. It contains some (probably unprecise and biased) theory and a new demonstration.

[Wheelz]

I can see some real benefits in this method when applied to the rest of my spreadsheet, although more complex.
I have additional sheets giving comparative performance within the individual "Grades". Although it's a duplicated display of existing data, it gives the ability to monitor performance within the competitor's own level with ease.

This method may take some time for my brain to understand fully but I will pursue it as a means of better utilisation of my data.

*FYI, this document will be displayed live on an overhead screen as the competition progresses allowing competitors to monitor progress in real time.
*Explanation- eg. A score of "97.05". The ".05" is not a decimal value but an indicator of how many shots were central in the bull over a 10-shot card. The difference between a 10 and a 10.01 can be seen on the top row of the attached image.

[Lupp]

I hadn't noticed that the alternative ranking formula in the second demo only allowed for a maximum of two equal ranks.

The new example is simplified under some aspects and allows for arbitrary many equal ranks.

(Just for curiosity: If rank 11 has the same score as rank 10 and is only made eleventh because the ranking is disambiguated stable - how is this handled if being among the Top Ten is a "special honour"? Schon't all those of same rank as the tenth also be listed?)

[Wheelz]

Yes, you are correct.
The Top 10 (or 11 if they are tied) then have a "shoot-off" to decide 1st, 2nd, 3rd regardless of grade.
Although it is possible, it is also very unlikely that after 3 x 10-shot cards + a shoot-off card have been completed there would still be a tie.
I probably should have attached the entire workbook to give the global perspective.
File is too large for this forum but is available here: http://www.tsw.net.nz/files/Open%20Workbook.ods