ELO ranking

[mw52563]

Hi,
I'm hoping someone can help Im trying to create a website to rank football teams based on an ELO ranking system similar to what chess players use... basically both teams start with 1000 points... the home team gives 7* of their 1000 points into a kitty and the away team gives 5% of their ranking points. The winning team takes all the points. I cant upload the spreadsheet, so Ive included the formula Ive used and a screenshot of the spreadsheet. I can get it to work for either the home team or away team but not for both... anyway this is the current formula

=IF(AND($C3=$F$1:$Y$1,$E3="a"),F2*0.07+(0.05*LOOKUP($A3,$F$1:$Y$1,$F2:$Y2)+F2),IF(AND($C3=$F$1:$Y$1,$E3="h"),F2-(F2*0.05+(0.07*LOOKUP($A3,$F$1:$Y$1,$F2:$Y2))),F2))

And this is how Ive set up the spreadsheet

Untitled.jpg

Thanks for any help
Regards
Mike

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Pete_UK]

I remember doing something like this for another poster some time ago - I'll see if I can dig up the file.

In the meantime, do as Ali suggests and attach a sample workbook.

Pete

[Pete_UK]

Here's the thread I was referring to, from March last year:

https://www.excelforum.com/excel-for...t-columns.html

It's quite a long thread, so it's probably worthwhile to work your way through it.

Hope this helps.

Pete

[mw52563]

Ok thanks Ill have a look through it now... I cant upload my table because when i click the attachment icon nothing happens except a thin box with nothing inside it

[AliGW]

You didn't read my instructions properly!!!

I said this:

... Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen. ...

[mw52563]

Yep, definitley didnt see that, apologies

[Pete_UK]

I don't know if you have managed to progress with this, but it strikes me that your file is quite a bit different than the one referred to in the other thread. That was concerned with wrestling matches, and the OP had arranged his data so that the winner of the bout was always in the first column (draws were quite rare, but my formula also accounted for those eventually). For football matches the Home and Away teams are quite important, so you might have to retain those columns and arrange to pick out the winning team in a particular column and the losing team in another column, and have these columns as the drivers for your ranking if you want to use a similar approach in the ELO formulae.

Hope this helps.

Pete

[mw52563]

Evening,
Yes Ive got it to work, I used yours as a template, and did exactly what you said basically. I arranged the teams with the winning team in the left column... Took a bit of messing about but it does work. Ignore all the stuff with logs on the right of the 'match' sheet. Im trying to do what this bloke has done
stuartlacy.co.uk/2017/08/31/implementing-an-elo-rating-system-for-european-football I'm not allowed to post links yet, but its basically that URL
Thanks for all your help, it was much appreciated
Regards
Mike

[Pete_UK]

That's great to see - thanks for posting this. Only a few more games to add to bring it up to date, and then only a few more weeks left till the end of the season.

It will be interesting to see how the ELO ranking compares with the finished league tables, as it will be possible to construct both from the data that you have.

Very impressive.

Pete

By the way, you should be able to post links now that you have more than 5 posts (that's a feature to deter spammers).