Elo Ranking

Hi!

This is my first post here!

I have developed a ranking system based on the ELO system (used e.g. in the Chess world). It works fine, but I have problems how to input my data and to update it.

What I want is to be able to input all the matches (football, console gaming) and results every now and then; fill in with more and more results after games have being played. I want excel to calculate the NEW RATING of the team after each match. Because the ranking system is based on your CURRENT ranking, it has to update the results in the correct order.

E.g., if I have entered these results in columns and rows:

Doncaster 0-2 Ipswich
Ipswich 0-1 Reading

Let's say Ipswich have a rating of 1200 points before these matches begin. After the Doncaster match it has 1210 points. Now I want excel to calculate the Reading match with THIS NEW RATING. Since Reading has a rating of e.g. 1320, Ipswich will only lose 7 points in this match, hence the new rating of Ipswich is 1203.

Is this understandable? I desperately need help with this, thanks!!

the well known ELO system

It may help to post an example workbook, with some examples included of what would change, and the expected result.

I personally am quite familiar with mathematics, and some algebra, and some beginner level VBA code, however, I've never heard of the ELO system, nor would I be able to guess what it is if I got ten tries.

Show me what you need it to do, and I"ll like be able to help you do it though.

mew!

Heh...well-known...to everyone who's heard of it.

First of all, I'm so sorry I used the term well known, I will edit the post immediately and remove these words.

Actually I shouldn't have mentioned it, because it's really not important. I will make a new excel file that just cover my problem:

Example.jpg

In column B we have the name of 4 boys and to the right of them is how many apples they have at the moment. Now they play many games against each other (column E vs H) and the result in these matches (column F vs G) will determine how many apples they will get.

I want excel to first look up how many apples E3 and H3 has, then do a calculation (this is NOT important) of the result in F3 and G3 which will determine how many apples E3 and H3 will win or lose (depending on the result in F3 and G3. Then E3 and H3's new amount of apples should change in the C column.

E.g, Boris had 8 apples from the beginning, but after his match against John he gain 2 new apples. I want C5 to change to 10. John lost 2 apples, so I want C6 to change to 7.

After this calculation has been made, I want excel to continue with the match in row 4 in the same way as it did in match 3. John now loses over Adam and will lose 3 apples, Adam will gain the same amount. So now I want C6 to change to 7-3=4 and C7 to 5+3=8.

So it will continue and calculate row after row. One of my problems has been circle reference.

Please help me with this calculation! Thanks!

Wow, thanks a lot mubashir aziz!! I will definitely learn something out of this.

But I did a big mistake when I presented my problem in my last post. I should have attached my real file instead of making a more simple version of it. So here it is:

Rank Games Project.xlsx

In column F, H, I and J I want to fill in results from different matches. I want this to be as long as necessary and continue to fill in with time.

My problem is the updating. Everyone starts at 1200 in rating. After the first match, bla vs nor, nor's new rating is 1209. What I want now is that when Excel calculate the next match, nor vs bar, the rating where it gets the nor's rating (C14) should have changed to 1209. The new rating for nor will then be 1219 and not 1211.

How is this possible? Excel should calculate the first result (row 2), then it updates the new rating of the teams involved. After this it calculates the next result (row 3) based on these new ratings and updates if after the calculation. And so on...

Thanks for all possible help!!

Hi,

check this again and I've highlighted some column as i don't think there is any need of these columns......

See if the attached does more or less what you want. The scores have a formula that makes them random at the moment; press F9 to see different results. You can hide most of the columns if you wish.

Hi,

I didn't make any change in Q column and i have checked that there is no change in Q column, please confirm in which column you made some changes.

Also, you should tried shg file which will be definitely helpful for you .....

Mubashir Aziz, it was column Q in my old Excel-file, column S in the one you posted to me. What is shg-file? (Sorry for my incompetence..)

Thanks shg! I will have a closer look at your file later today.

Shg, thanks so much for letting me look at that file, it helped me so incredible much!!!

No I have one more problem at the moment. In column C I want the latest ranking to be next to the name in column B. So ackba floyd should have 1281 points; the value in V11 should be in C4; value W11 should be in C5 and so on. But if there is a new game played and a new value has been calculated in V12, I want Excel to see this and return V12 to C4; W12 to C5 and so on.

Can someone help me with this please? Thanks a lot!

Rank Games Project v6.xlsx

In column C I want the latest ranking to be next to the name in column B.

There is nothing in column B or C ...

In C3, =MATCH(1E+100,V:V, 1) - ROW(V1) + 1

In C4 and copy down, =HLOOKUP(B4, $V$1:$BS$50, C$3, FALSE)

You're welcome. Would you please mark the thread as Solved? (See How To ... in menu bar.)

Ok, I marked it as solved, thanks again! If I have small very easy questions, should a make a new topic in this forum then?

If they are exactly related to your prior question, you can resume here; if not, please start a new thread.

Originally Posted by shg

See if the attached does more or less what you want. The scores have a formula that makes them random at the moment; press F9 to see different results. You can hide most of the columns if you wish.

Thanks a lot for sharing this, it was exactly what I was looking for. I'm testing it and it works nice.
We are using it for individual player ranking in our internal lunch-time office championship in fussbal (kicker, table football)

I have one qestion regarding this file - does goal difference in one game (for example +8 comparing to +5) makes better rating for a player like it works for World Football Elo Ratings?

The ratings are based on the following formula:
Rn = Ro + KG(W − We)
or
P = KG(W − We)

Where:
Rn = The new team rating
Ro = The old team rating
K = Weight index regarding the tournament of the match
G = A number from the index of goal differences
W = The result of the match
We = The expected result
P = Points Change

Update: sorry, the goal difference doesnt work on raiting unfortunatelly. Can somebody help me with updating the file to formula Rn = Ro + KG(W − We)&
I'm trying but doesnt work properely as almost beginner in Excel.