Rating System Auto-Update?

[Mike2020]

Hello,

I am trying to make a spreadsheet to do the following for a table tennis league at work.. I have my own "elo rating style" rating system.

Name.......... Rating

Beta........... 1100
Charlie........ 1012
Alpha........... 998
Delta........... 800


I want to continue to update a RESULTS sheet with something like one column for the winner, one column for the loser.. and maybe one column for the date?

In any case, I want it to update the ratings by looking at the addition to the Winner/Loser's Column and retrieving the two corresponding CURRENT rating scores.. inputing them into a formula that spits out the two new rating scores

For Example:

Beta defeats Alpha
Take Beta (1100 rating) and take Alpha (998 rating). Subtract to get the difference.. Divide by 2 (formula actually has sq root and is more complicated but for our purposes a value of 2 is fine) and then adds that value to the winner while subtracting that value from the loser.

If I update the actual value, then i've altered the original data and the formula breaks down.

I think i might need to use some type of hidden tables in addition to the hidden tables i'm currently using. However, I'd like to get others opinions on how to accomplish this in the best way.

To recap:

Enter Results
Ratings Auto-Update based on new data
Standings update based on new rating data

Is Access or another program better suited for this? or do I need to do some VBA? Macros?



I've tried to do this but as so

[psumvp]

Here is an example of how to do it... There is a seperate section with each player's ratings, their initial rating (which you can hide), and their current rating. There is also a section with each of the games result and the net change in rating as a result of that game. The change and current rating take into account all previous changes in scores.

[Mike2020]

psumvp- Thanks. While that's a great way to do it from the way I was doing it, it still has one flaw. It uses only the original rating.

For example, if you set starting rating to 1000, you can see it more clearly. No matter what the players CURRENT rating is, the ratings change will always be dependent on the initial rating.

Say I set the minimum change to ABS(10+(Vlookup(winner initial rating) - vlookup(loser initial rating))) (so 10 is the minimum change).

For example, if everyone starts off at 1000, and Delta defeats Alpha. Delta should go up +10 to 1010 while Alpha drops -10 to 990. However, if Delta defeats Alpha again, the point change is still set at 10 instead of the true change of 20.

If I modify it to use the CURRENT rating, excel gives me errors of circular algorithms.

Hence, I'm trying to figure out how to use the current rating in the current results.

Thanks for helping though, that was a very neat way of trying to solve the problem! I learned a lot.

More help is greatly appreciated!

[psumvp]

My Bad... I created the formula in the cells below where it was calucalating and never pasted them up. I have reattached the spreadsheet with the correct formulas...

The current rating is for reference only, you should not be doing calculations on it... All of the calculations occur on the change field, you just figure it all of the previous win/loss rating deltas. So for the rating it should be abs(10 +(winner's initial rating + winner's previous game's won CHANGES - winner's previous game's loss CHANGES) - (loser's initial rating + loser's previous game's won CHANGES - loser's previous game's loss CHANGES))/2

[Mike2020]

Wow. Very efficient. I'm good at logic and nested loops and other things I taught myself but wow.. these functions sure can make things easy if you know them and how to utilize them.

Two more questions:

1. Whats the most efficient way to total how many times BETA shows up in a column (ie. I'll probably add Wins/Losses columns). I can figure that out myself but I just need to know which function I should research..

2. How about remembering each player's HIGHEST rating achieved. (the current rating is just a reference that will fluctuate.. so I'm wondering if this will have to be a macro performed after each data entry?)

Thanks psumvp. You've been an awesome help!

[Mike2020]

I've figured out how to do the simple calculations..

=COUNTIF(J$2:J$500,$A2) with $A2..10 having the player's names while J$2-500 having the winners of the matches..

However, I'm still stuck on to how to do my #2 problem.