+ Reply to Thread
Results 1 to 7 of 7

rank order by up to 4 progressive criteria

  1. #1
    Registered User
    Join Date
    08-06-2019
    Location
    Northern Ireland
    MS-Off Ver
    2011 version 14.0.0 for Mac
    Posts
    3

    rank order by up to 4 progressive criteria

    Hi all

    Haven't posted for a while, but am working on a golf competition spreadsheet that ranks the order of the candidates, eliminating ties.

    I have completed the first stage using =RANK(C2,$B$2:$G$2, )+COUNTIFS($B$2:$G$2,C$2,$B$3:$G$3,">"&C$3) which if there is a tie I can resolve it if there is a difference in values in row 3.

    However I can't get beyond this, in the event there is still a tie, to look at row 4 etc.

    Is this possible given the data and rows available in the attached spreadsheet?

    Thanks
    Gordon
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: rank order by up to 4 progressive criteria

    Haven't posted for a while,
    You have NEVER posted before with this account ... This was your first post - welcome!

    I'll have a look at your workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: rank order by up to 4 progressive criteria

    Two proposals.
    Rank or ranks
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Rank of sums
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-06-2019
    Location
    Northern Ireland
    MS-Off Ver
    2011 version 14.0.0 for Mac
    Posts
    3

    Re: rank order by up to 4 progressive criteria

    Thanks DJunqueira

    I have looked at your suggestions, your first one doesn't produce the correct result. The second one does appear to progress further, but fails if I enter some different numbers into the grid.

    To clarify, this should be the intended processing:
    If there are ties when ranking row 2, look at row 3 to see if they can be differentiated, (higher number wins)
    If there is still a tie, check row 4 for differences.
    If there is still a tie, check row 5
    If there is still a tie check row 6.

    Hope this helps.
    Thanks again

  5. #5
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: rank order by up to 4 progressive criteria

    Lets see this one.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-06-2019
    Location
    Northern Ireland
    MS-Off Ver
    2011 version 14.0.0 for Mac
    Posts
    3

    Re: rank order by up to 4 progressive criteria

    Thanks again,

    Took my time to consider this suggestion and see how it was working. I also put it into the spreadsheet and played about with entering different values, to see if your calculation worked for different permutations.

    So far I have not been able to find a situation where it gives the incorrect rank, when comparing at the scores manually.

    I will happily use this formula in future.

    Thanks again

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: rank order by up to 4 progressive criteria

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

    Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) 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 each of those who offered help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to fill cells in order by known rank until overall criteria is met.
    By Kniggit88 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2022, 04:38 PM
  2. [SOLVED] Rank/Order including manual overriding rank/order input
    By 77highland in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-15-2021, 06:38 PM
  3. assistance with match and rank order formula based on multiple criteria
    By OSepulvedaIII in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-18-2016, 06:21 AM
  4. Rank Teams in Performance Order - not as easy as just =Rank...
    By excelnat in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2015, 12:12 PM
  5. Densed Rank with 2 critieria with Alphabetical Order as one criteria
    By hamson in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-19-2013, 06:15 AM
  6. [SOLVED] Check if the values of a list are in progressive order.
    By Askalian in forum Excel General
    Replies: 11
    Last Post: 01-24-2013, 08:56 PM
  7. Replies: 3
    Last Post: 05-01-2010, 10:23 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1