+ Reply to Thread
Results 1 to 10 of 10

ELO ranking

  1. #1
    Registered User
    Join Date
    10-27-2016
    Location
    Swansea
    MS-Off Ver
    2003
    Posts
    11

    ELO ranking

    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

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    25,389

    Re: ELO ranking

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,307

    Re: ELO ranking

    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

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,307

    Re: ELO ranking

    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

  5. #5
    Registered User
    Join Date
    10-27-2016
    Location
    Swansea
    MS-Off Ver
    2003
    Posts
    11

    Re: ELO ranking

    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

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    25,389

    Re: ELO ranking

    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. ...

  7. #7
    Registered User
    Join Date
    10-27-2016
    Location
    Swansea
    MS-Off Ver
    2003
    Posts
    11

    Re: ELO ranking

    Yep, definitley didnt see that, apologies
    Attached Files Attached Files

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,307

    Re: ELO ranking

    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

  9. #9
    Registered User
    Join Date
    10-27-2016
    Location
    Swansea
    MS-Off Ver
    2003
    Posts
    11

    Re: ELO ranking

    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
    Attached Files Attached Files

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    17,307

    Re: ELO ranking

    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).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1