+ Reply to Thread
Results 1 to 18 of 18

Retrieving ranking by looking at 2 criteria

  1. #1
    Registered User
    Join Date
    09-09-2018
    Location
    Enschede, Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Question Retrieving ranking by looking at 2 criteria

    I have a group phase tournament bracket and I would like to retrieve a winner and a runner-up based on two criteria: the amount of wins as most important criterion and in case of a draw: game duration. The team with the most wins and the lowest game duration would be #1. The wins and the average game duration are retrieved from a scoreboard. I would like to display the name that goes with the winner and the runner-up, as if it were structured this way:


    Team 1 | 1 wins | 12:00
    Team 2 | 1 wins | 11:00
    Team 3 | 3 wins | 10:00
    Team 4 | 1 wins | 14:00

    Winner:

    Team 3

    Runner-up:

    Team 2

    Feel free to work with that as if it were A1:C4. Thanks in advance.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Retrieving ranking by looking at 2 criteria

    Hi welcome to the forum.

    Use LARGE() function instead of RANK()

    The syntax is simple LARGE(the range, ?) where the ? is the number in the ranking you're looking for, so 1 is first 2 is second and 9 is the ninth value.
    It's a standard Excel Function
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    09-09-2018
    Location
    Enschede, Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Retrieving ranking by looking at 2 criteria

    Hi Keebellah,

    How exactly do I exclude the winner in the scenario that I posted, to look for the runner-up? The function has to detect who has won the most games and then check the game duration. The range to rank on game duration should exclude whoever has won on the first criterion but that specific cell can change depending on input in the scoreboard right? I am using LARGE to check for the winner, but beyond that?

    Thanks
    Last edited by H1dd3v; 09-09-2018 at 10:36 AM. Reason: spelling error

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Retrieving ranking by looking at 2 criteria

    You DON'T exclude 1 will return the first in ranking 2 will return the second
    Have you even tried it or are you just hollering without testing?

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Retrieving ranking by looking at 2 criteria

    I changed the format to the column B to a numeric format with a prefix
    Column C is your's to continue with
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-09-2018
    Location
    Enschede, Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Retrieving ranking by looking at 2 criteria

    I am aware of what you did -- I already posted that I knew how to do that. Integrating C into the equation so as to combine both criteria is what I struggle with. I get that it is probably an easy fix but considering that you are aware that I don't know the answer and that I ask the question, could you not bash me for it please? I don't know how to do it.

    Your usage of LARGE doesn't account for draws at all for the winner position by the way. Which is what I specifically asked.

  7. #7
    Registered User
    Join Date
    09-09-2018
    Location
    Enschede, Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Retrieving ranking by looking at 2 criteria

    For clarification, in the example the results should be:

    #1: Team 3
    #2: Team 2
    #3: Team 1
    #4: Team 4

    And I would like this to be the output of my sheet

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Retrieving ranking by looking at 2 criteria

    With a helper column
    =VALUE(B2&24-HOUR(C2))
    Then
    =INDEX(A$2:A$5,MATCH(LARGE(E$2:E$5,1),E$2:E$5,0))
    Attached Files Attached Files

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Retrieving ranking by looking at 2 criteria

    @Fluff13; If the value (the time) in Column C is what determines it then the runner-up is team 4 and not team 2
    Correct me if I'm wrong.
    And @H1dd3v: sorry for seeing it as 'bashing' that was and is not the intention, we are all here to learn, learn by asking ans also learn by helping, and we're never too old to learn, sorry for the 'bash'

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Retrieving ranking by looking at 2 criteria

    Try this one, the value in column B is not a string but a number
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Retrieving ranking by looking at 2 criteria

    Quote Originally Posted by Keebellah View Post
    @Fluff13; If the value (the time) in Column C is what determines it then the runner-up is team 4 and not team 2
    Correct me if I'm wrong.
    My understanding was that the shortest time was the tie-breaker, which is Team 2 at 11hours

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Retrieving ranking by looking at 2 criteria

    It that's true the mine is off

  13. #13
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Retrieving ranking by looking at 2 criteria

    Lets see what the OP says.
    Whichever of us is correct then the OP should have a working solution

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Retrieving ranking by looking at 2 criteria

    True, I did not consider the actual meaning of that column, if it's the shortest time in which the team scored then you're correct and my assumption is off.

  15. #15
    Registered User
    Join Date
    09-09-2018
    Location
    Enschede, Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Retrieving ranking by looking at 2 criteria

    Hi, both options seem quite close to what I want. The outcome of Fluff13 is indeed desired. However, game duration was not accurate to the range I'll be working with, that range will be times between 10 and 60 minutes. What I did to adjust his file is =VALUE(B2&60-MINUTE(C2)&60-SECOND(C2)) so as to approach it the same way; make the team with the lowest amount of minutes translate to the highest in the helper column. Now it works exactly as intended. Apology accepted by the way. Attached is the end result. It also taught me the index command. Thanks a lot!

    h6zuF7l.png

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Retrieving ranking by looking at 2 criteria

    Glad all this led to a solution,.
    No hope the best team wins

  17. #17
    Registered User
    Join Date
    09-09-2018
    Location
    Enschede, Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Retrieving ranking by looking at 2 criteria

    Somehow I hoped there would be a more elegant solution where the result of check 1 would be input for check 2 but I'm glad this works consistently.

  18. #18
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Retrieving ranking by looking at 2 criteria

    Hi Hidde,
    What do you mean?
    The function large returns the rank you want to find, so if you ask for the winner then it will return that and the second will be based upon the parameter 2 for runner-up but if you have two winners then I don’t know if it works
    The alternative would be a macro where you do the calculations in a function

+ 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. Ranking Question - Ranking based criteria but having forumla value issues
    By harp1983 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-09-2016, 07:03 AM
  2. Retrieving Data that meets a certian criteria
    By Otaishtf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-26-2013, 03:43 AM
  3. Replies: 7
    Last Post: 08-26-2011, 06:00 PM
  4. Replies: 0
    Last Post: 08-25-2011, 01:59 PM
  5. Retrieving an Item from a List that Meets Multiple Criteria
    By hgopp99 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-21-2006, 11:10 AM
  6. Replies: 3
    Last Post: 01-02-2006, 10:30 AM
  7. Retrieving an Item from a List that Meets Multiple Criteria
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-25-2005, 02:59 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