I've created a workbook for the 2011–12 NHL season so I can calculate standings based on what would happen if the NHL were to change their current standings format of Win-Regulation Loss-Overtime/Shootout Loss to a basic Win-Loss format, where standings are based on Win Percentage instead of Points.
To do this, I've used a workbook of the 2011–12 schedule created by Dirk Hoag of On the Forecheck, where I can enter the results of each game in the sheet labeled "Results", and all the team's stats will be calculated automatically using formulas and reflected in 3 standings tables, on the sheets "League", "Conference" and "Division" (for the entire league standings, the standings of the two conferences, and the standings of the six divisions, respectively). I found formulas to calculate each team's stats, i.e. Wins, Losses, Home/Away Record, Goals For/Against/Difference, etc. from various places on the internet, especially here.
Attached is the workbook, but here's a sample formula, used to calculate the Home Wins for the team whose name appears in B2 on the sheet labeled "League":
=SUMPRODUCT((Results!G$2:G$1231=B2)*(Results!D$2:D$1231<=Results!E$2:E$1231)*(Results!C$2:C$1231<=Results!F$2:F$1231)*(Results!E$2:E$1231<>""))
(Since the season hasn't started, I've entered some hypothetical results to test the formulas.)
The only thing I can't figure out how to calculate are the columns labeled "L10" and "Streak". "L10" means the team's Win-Loss record in their last 10 games, and "Streak" is the team's current Win-Loss streak. Can anyone figure out formulas I can use to calculate these? I only need the formula to be used on the sheet named "League", I can transpose info to use them on the other two sheets. Also, I'd prefer a formula, as I'm not familiar with using VBA. Thanks in advance.
Bookmarks