+ Reply to Thread
Results 1 to 3 of 3

Grab next same value in Excel/Google Sheets for RANK/MATCH formula

  1. #1
    Registered User
    Join Date
    02-17-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    1

    Post Grab next same value in Excel/Google Sheets for RANK/MATCH formula

    I have a list of players in Google Sheets that assigns them a rank based on wins, followed by some more match variables — standard stuff.

    I then use the rank to order the players in a league table format.

    However, when I have two more players with the same rank, I hit a problem. The easiest way to illustrate it is with a link to the spreadsheet. Left side is the data, right side is the outputted table:

    https://docs.google.com/spreadsheets...it?usp=sharing

    My aim is:

    - To allow drawing players, and thus output:

    Please Login or Register  to view this content.
    - But also to add players with a rank of 0 (already calculated) to the bottom of the list, in no particular order, but below those who have played.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Grab next same value in Excel/Google Sheets for RANK/MATCH formula

    First off, you never want to use Random when breaking a tie, since those values will change every time the sheet is re-calculated.

    The best way to get around draws is to add a small tie-breaking value to the value that is being ranked, one that does not change the value by much. For example, in football, 3 points for a win, 1 point for a draw. That gives you points that are ranked - but goal difference is used to break ties, then goals scored. So for that I use a formula like

    =I4+H4/100+F4/10000-ROW()/1000000000

    Where I4 has the points (3* # wins + # draws), H4 has the goal difference, and F4 has goals scores. The -ROW()/1000000000 is a value that breaks ties, and is predictable but small. Because my teams are listed alphabetically, the end result is that the teams are ranked alphabetically if everything else is tied, and this works if there are 2 ties or 100 ties. For your situation, players with 0 points would sort to the bottom using a similar formula.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Grab next same value in Excel/Google Sheets for RANK/MATCH formula

    Another option for tie breaks is to use the countif() function, along with the divisor. I tend to keep the divisor small, say 100 times more than potential counts, but any method that adds a small value is good
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Replies: 0
    Last Post: 12-03-2014, 05:10 PM
  2. Basic Sum Formula in Google Sheets
    By lesoies in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 7
    Last Post: 08-27-2013, 04:05 PM
  3. Google Docs - Help with Lookup/Match Formula
    By livifivil in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 06-24-2013, 01:20 PM
  4. Replies: 0
    Last Post: 12-28-2012, 07:06 AM
  5. Excel Match and Grab Formula Help
    By johnlynches in forum Excel General
    Replies: 4
    Last Post: 01-12-2012, 09:01 PM

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