Results 1 to 7 of 7

Thread: In Search of an Excel Wizard

  1. #1
    Location
    Beaumont, AB
    Rep Power
    50

    Dobber Sports Icon

    Default In Search of an Excel Wizard

    In my 31 team sim league, we use a complicated "formula" to rank our goalies in order to give them new ratings each year. This ranking system was my creation about 5 years ago so it is also my responsibility to put the data in from each new season to produce our new rankings each off-season. Every year it has taken me a much too high number of hours to do it because I am no Excel Expert. I am hoping someone here can help me create a template to do this quickly and efficiently each year.

    Here's what I have to do each NHL season.

    1) List all of the goalies who played an NHL minute that season
    2) Rank them from first to last for the 5 categories we use (Wins, Goals Against Average, Save Percentage, 5v5 Save Percentage and Shutouts)
    3) Adjust the rank for the rate stats (GAA, SV%, 5v5SV%) based on games played. In a normal NHL season if a goalie played 40 or more games their rank is not changed.
    35-39 GP, they get a +10 to their rank
    30-34 GP = +15
    25-29 GP = +20
    20 - 24 GP = +25
    15 - 19 GP = +30
    10 - 14 GP = +35
    5 - 9 GP = +40
    2 - 5 GP = +60
    1 GP = +80
    This makes it so NHL starters are more valuable than back up goalies which is what we want.
    4) Re-rank the 5 stats with these adjustments
    5) Get a weighted average of the 5 adjusted rankings (W=33%, GAA=25%, SV%=20%, 5v5SV%=17%, SO=5%)
    6) Rank the players based on the weighted average.
    7) Combine the rankings of the most recent NHL season with the previous NHL season with each year being worth 50%. ( I combine the ranking for each stat instead of the overall ranking as I believe it is more accurate)
    8) Rank these combined weighted averages to get our ranking for our upcoming season.

    The way my spreadsheet is currently set up, I need to use about 3 different sheets to accomplish this and have to manually copy information from one sheet to another. The biggest reason for this is because I don't know how to rank a data point containing a formula without affecting the data points used in the formula.

    There must be a better way so I am looking for your help!

  2. #2
    Rep Power
    14

    Dobber Sports Initiate

    Default Re: In Search of an Excel Wizard

    Hello! I'm maybe the wizard...in fact, I do that as a job... not beeing a wizard but the Excel thing as a Industrial engineer.

    Didn't take me long you just need to put it in a Table to get the formula to stick. Since I don't know how to tell you, I'm gonna show you. I made the 2019-2020 season with what i think you want

    Here's the link: https://docs.google.com/spreadsheets...t#gid=76576484

    Don't know if the link works, if it doesn't just tell me

    Hope it help
    31 TEAMS keep all multi-cat (lot of them) dynasty
    Offensive stats : Goals, Assist , PPP, PPTOI, SOG...
    Defensive stats : PKTOI, BLKS, TakeAway, Giveaway...
    (basically best NHL player)


  3. #3
    Location
    Beaumont, AB
    Rep Power
    50

    Dobber Sports Icon

    Default Re: In Search of an Excel Wizard

    I had to request access to open the file so I have done that.

    This would be awesome if it works! Thank you for wasting your time to help me!

  4. #4
    Rep Power
    14

    Dobber Sports Initiate

    Default Re: In Search of an Excel Wizard

    I never work on google sheet but I just accepted your request.

    No worries, like I said I work with Excel everyday, the least I can do for a fellow forumers(??)

    tell me if there's anything
    31 TEAMS keep all multi-cat (lot of them) dynasty
    Offensive stats : Goals, Assist , PPP, PPTOI, SOG...
    Defensive stats : PKTOI, BLKS, TakeAway, Giveaway...
    (basically best NHL player)


  5. #5
    Location
    Nova Scotia
    Rep Power
    40

    Dobber Sports Deity

    Default Re: In Search of an Excel Wizard

    Quote Originally Posted by PicklePickle View Post
    Hello! I'm maybe the wizard...in fact, I do that as a job... not beeing a wizard but the Excel thing as a Industrial engineer.

    Didn't take me long you just need to put it in a Table to get the formula to stick. Since I don't know how to tell you, I'm gonna show you. I made the 2019-2020 season with what i think you want

    Here's the link: https://docs.google.com/spreadsheets...t#gid=76576484

    Don't know if the link works, if it doesn't just tell me

    Hope it help
    It opened for me. Nicely done! +1 for your effort.

    So is the spreadsheet I am seeing, and the numbers down the side, how they rank overall?
    10 Team, Points Only, Cash League

    25 Man Roster (no position), top 20 point getters count at end of month
    Keep 20/25 at seasons end, Cut 5 to FA for redrafting
    Goalie points W=2pt L=-1pt SHO=2pt

    Stamkos, Tavares, Eichel, Mercer, JRobertson, RThomas, Kucherov, Nugent-Hopkins, Tuch, KConnor, Necas, Point, Konecny, SJarvis, Cozenz, Morrissey, Bouchard, Josi, Novak, Tolvanen, Peterka, Brink

    G- Vasilevskiy, Sorokin, Oettinger


    "Cleavage is like the sun. You can look, but dont stare.. Unless you're wearing sunglasses."

  6. #6
    Rep Power
    14

    Dobber Sports Initiate

    Default Re: In Search of an Excel Wizard

    Quote Originally Posted by Axeman33 View Post
    It opened for me. Nicely done! +1 for your effort.

    So is the spreadsheet I am seeing, and the numbers down the side, how they rank overall?
    The last number (total ranking) on the right is the overall ranking like he asked (with the ajustement for the GP and with the % worth)

    I've put it in a table, I don't understand why google sheet doesn't do it automatically. It should be easier like that You can filter whatever stats or ranking you want
    31 TEAMS keep all multi-cat (lot of them) dynasty
    Offensive stats : Goals, Assist , PPP, PPTOI, SOG...
    Defensive stats : PKTOI, BLKS, TakeAway, Giveaway...
    (basically best NHL player)


  7. #7
    Location
    Nova Scotia
    Rep Power
    40

    Dobber Sports Deity

    Default Re: In Search of an Excel Wizard

    Quote Originally Posted by PicklePickle View Post
    The last number (total ranking) on the right is the overall ranking like he asked (with the ajustement for the GP and with the % worth)

    I've put it in a table, I don't understand why google sheet doesn't do it automatically. It should be easier like that You can filter whatever stats or ranking you want
    Gotcha. Ya, the way they names list is their overall ranking when I first opened up the spreadsheet. Rask, Hellebuyck and Vasilevskiy are the top three by your rankings. Some interesting names in that top 15 that I bet most wouldnt have expected.

    Again, great stuff.
    10 Team, Points Only, Cash League

    25 Man Roster (no position), top 20 point getters count at end of month
    Keep 20/25 at seasons end, Cut 5 to FA for redrafting
    Goalie points W=2pt L=-1pt SHO=2pt

    Stamkos, Tavares, Eichel, Mercer, JRobertson, RThomas, Kucherov, Nugent-Hopkins, Tuch, KConnor, Necas, Point, Konecny, SJarvis, Cozenz, Morrissey, Bouchard, Josi, Novak, Tolvanen, Peterka, Brink

    G- Vasilevskiy, Sorokin, Oettinger


    "Cleavage is like the sun. You can look, but dont stare.. Unless you're wearing sunglasses."

Posting Permissions

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