Tuesday, June 28, 2011

Excel's RandBetween Function Picks Next Year's NHL Season

For those of you not familiar with Excel, the RandBetween formula generates a random number between specified high and low boundaries.  I thought it would be fun to let this function pick NHL playoff teams for the following season.


How it was done:
I figured that each NHL team would be within one standard deviation (7) of its wins total from the previous season, and within the high (13) and low (5) values for shootout and overtime losses.  With the generator filling in those two columns, the losses column could be filled in by however many more games each team needed to hit 82.

To put that in simpler terms, the wins and overtime and shootout losses formulas for each team looked like this respectively:
=randbetween(x-7,x+7) x = that team's wins in 2011
=randbetween(5,13)

Of course going through that for each team was likely to produce an (impossible) uneven number of wins and losses.  So I created another modifier that picked teams at random and subtracted a win (and added a loss) from/to their standings until the columns were equal.  These are your 2011-2012 standings as chosen by Excel:

Eastern Conference
  1. Pittsburgh - 53-22-7 (113 pts)
  2. Tampa Bay - 50-24-8 (108 pts)
  3. Boston - 49-23-10 (108 pts)
  4. Philadelphia - 47-23-12 (107 pts)
  5. Buffalo - 46-26-10 (102 pts)
  6. Montreal - 39-30-13 (91 pts)
  7. Toronto - 39-30-13 (91 pts)
  8. Carolina - 42-34-6 (90 pts)
  9. Washington - 40-32-10 (90 pts)
  10. Ottawa - 38-33-11 (87 pts)
  11. Winnipeg - 38-33-11 (87 pts)
  12. NYR - 37-34-11 (85 pts)
  13. New Jersey - 35-41-6 (75 pts)
  14. NYI - 29-46-7 (65 pts)
  15. Florida - 26-45-11 (63 pts)
  16.  
Western Conference
  1. San Jose - 55-17-10 (120 pts)
  2. Chicago - 51-23-8 (110 pts)
  3. Vancouver - 54-19-9 (107 pts)
  4. Detroit - 48-26-8 (104 pts)
  5. Nashville - 47-27-8 (102 pts)
  6. Los Angeles - 46-27-9 (101 pts)
  7. Dallas - 45-28-9 (99 pts)
  8. Anaheim - 44-28-10 (98 pts)
  9. Phoenix - 42-35-5 (89 pts)
  10. St. Louis - 38-34-10 (86 pts)
  11. Columbus - 38-37-7 (83 pts)
  12. Minnesota - 35-36-11 (81 pts)
  13. Calgary - 32-41-9 (73 pts)
  14. Colorado - 27-49-6 (60 pts)
  15. Edmonton - 20-50-12 (52 pts)

No comments:

Post a Comment