# Having trouble getting rating sheet to update as games are entered

I keep a spreadsheet that uses the elo equation for chess where I work that updates players ratings when I enter the two players names and who won or lost (1 for win, 0 for loss). My issue is that it takes a lot of manual work whenever I run out of space or when new players enter the system. My question is, is there a more efficient way to do this? I tried using an arrayformula for the elo equation part of it but that won't work since it is circular, and I don't know enough about JavaScript to try and write a custom script to do the work. Any help appreciated.

Here is an example of the spreadsheet, can't post the original since it has last names but it has over 500 games in it: https://docs.google.com/spreadsheets/d/1aKln4OkukbZyieISrpM7SaQRNZGhPeMKSqrXQAx-BJc/edit#gid=858758425

• Is it really a lot of work? You can add, say, 1000 rows at once and copy-paste the formula to all of them, making room for 1000 games ahead. Similarly, copy-paste a column when adding a new player (something you have to do manually anyway) doesn't seem very hard. Automation with a script is possible, but you'd need to re-implement the ELO formula from scratch, and the result may be xkcd.com/1319 – user79865 Jan 29 '16 at 2:42

If you're interested in use (more than in development), you should give a try to rankade, our ranking system. It's free to use and it's designed to manage rankings (and stats, and more) for small or large groups of players.

Rankade doesn't use Elo, but its algorithm (called ree algorithm), although more complex (here's a comparison), is similar to Elo if you play 1-on-1 matches only.

After every match, only the ranks of the two players change. So, instead of calculating the rank of every player after a match, just calculate the new rank of the players.

The trick is, say Alice played Bob, you need to find the last time Alice played and her rank at that time, as well as the last time Bob played and his rank at that time.

This spreadsheet example shows you how to find the last time a player played.

Let's say you put updated ranks in column G (for player 1) and F (for player 2). The last time Alice appears in column A is row x. The last time she appears in column C is row y. And x > y. Then her last rank would be `indirect("G" & x)`

The spreadsheet uses `indirect` function, so you can freely append new record without breaking the formula.

• I have been trying to use this on and off for a few days now. I copied your sheet and have been using that as the base. I have been having issues. From my experience using this formula, I would still have to copy/drag the formula into all of the cells that I want it used in. Also I couldn't get the max equation to work the way it is listed in your last column. In mine you'll see that I copied the equation and dragged it down. docs.google.com/spreadsheets/d/… – user113683 Feb 3 '16 at 0:40
• there was a small mistake. `row()` should be `row()-1`, otherwise it will count the current row and hence `max()` always return the current row number. try docs.google.com/spreadsheets/d/… – daniel Feb 3 '16 at 2:37
• note that because the formula uses `indirect`, when you move columns, the formula won't update the reference correctly. You need to manually update the `indirect` when you move columns. – daniel Feb 3 '16 at 2:40
• I think this might be amazing. So from what I can figure, with this, I wouldn't need all of the names going across the top at all. and for the C and F columns I would just use the elo equation with the ranks in the J and M columns? In the example sheet it just has the updated ranks but it doesn't show where they came from. Then I could use the indirect formula to rank the players on a separate sheet. – user113683 Feb 4 '16 at 0:16