• Hi,

I have a list of numbers entered in rows.

Each row denotes one measurement of compliance.

For anonymity, I removed the names of 23 team members in the top row and replaced them with numbers from 1 to 23.

I'm struggling with the following: I'd like to set up a leader board and for that I would need to know (at least for the last row in this list) who the top 5 performers are. Performance is the number in the row, e.g. in the attached example sheet, "Mr.19" is the best performer in this last measurement because he's got the highest score (57), with "Mr.22" being second with 40 consecutive submissions.

Please, can you help me with a formula that would identify the top 5 performers, say, in columns Z to AE. There could be a tie in one of the top 5 places, so I added a column for the 6th placed.

Thank you

## Files

• Hi,

You can use following formula

Code
``=INDEX(\$A\$1:\$X\$1,MATCH(LARGE(\$B\$615:\$X\$615,RIGHT(Z1,1)),\$A\$615:\$X\$615,0))``

See attached file

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Thank you very much. I knew it must be the Index formula, it's just I couldn't put it together myself. This way, I can learn for the future.

Much appreciated!

• Thanks for your Thanks AND for the Like

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi Carim,

I have an additional question. How can I also identify another member that has the same (one of Top5) performance spots?

Please, see the attached file. In it, "Mr.3" with 16 consecutive submissions is in the 3rd place, but so should be "Mr. 10". Is there a way to capture that, too?

Thanks.

## Files

• Hi,

Whenever you are facing tied scores, you do need to establish a specific rule in order to deal with tie breaking ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi Carim,

yes, you're right. If they'd be tied, I'd use some other metric to position one or the other ahead, but for this exercise (as I can then pick it up from there myself) I'd just go with the alphabetical names. Since I replaced their names with numbers (like "Mr.3" instead of "John"), let use this rule in case of a tie: the smaller the number, the better the position. So, in case of the example, it would be Mr. 3 before Mr. 10.

I hope it makes sense to you.

Thanks.

Hi,

Whenever you are facing tied scores, you do need to establish a specific rule in order to deal with tie breaking ...

• Hi,

Indeed you can design your tailor-made formula to break the ties you could get.

Quite often a set of helper columns is required, unless you would go the UDF route

For your own inspiration, below is the basic standard formula to differentiate rankings:

Code
`` =RANK(B598,\$B598:\$X598,1)+COUNTIF(\$B598:\$B598,B598)-1``

Attached is an illustration with your test file

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi Carim,

thank you for the formula and the file as well!

I'll have a look and see how this RANK and COUNTIF work together. Am a bit slow in the head.

I'm Ok with having extra columns with formulas in the sheet, so no need for invoking Visual Basic.

Thank you!

• You are welcome

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!