How to identify leaders?

    • New

    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

    • New

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

    • New

    Thanks for your Thanks AND for the Like :thumbup:

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

    • New

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

    Like 1
    • New

    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.

    Quote from Carim

    Hi,


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

    • New

    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

    • New

    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!

    • New

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

    Like 1

Participate now!

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

Register Yourself Login