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

**jpsear** · 02-17-2015, 06:28 PM

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.

**Bernie Deitrick** · 02-17-2015, 07:42 PM

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.

**FDibbins** · 02-17-2015, 07:52 PM

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