Halo Multiplayer Stats Spreadsheet

We got this note from Greg Irvine recently - it's a pair of spreadsheets (one empty, one populated with real data as an example) that can help you keep track of player stats. I haven't had time to play with this (more than simply open the sheet) - but if you're serious about keeping track of how people in your group are doing, this might be helpful!

From: "Irvine, Greg" <greg.irvine@eds.com>
To: "'halo@bungie.org'" <halo@bungie.org>
Subject: Halo Multiplayer Spreadsheet.
Date: Thu, 26 Feb 2004 15:32:48 +1100

To the team at HBO,

Can you please make the following spreadsheet available to your subscribers.

Over the past year or so, I've hosted a number of Halo Multiplayer LAN parties at my place. Over this time, we have developed an Excel spreadsheet to record the stats for the day's play. This allows us to monitor each player's performance and declare an overall winner for the day. It also allows us to use a "Team Balancer" function to select the most evenly-matched teams. I have included both a blank spreadsheet (190k, zipped) and one populated with the scores from our last day (194k, zipped). Looking at the completed example is probably the best way to get a feel for how the sheet is used.

Regards,
Greg Irvine
Sydney, Australia.

How to use it:

The spreadsheet contains 23 individual sheets. This includes the "Totals" sheet, 20 game-stats sheets, a "Teamwork Analysis" sheet, and the "Team Balancer" sheet.

The "Totals" page is where the overall stats are viewed. The player names are entered in column A, and there is a "Team Win Weightings?" option you can set to "Y" or "N". We recommend leaving the Team Win Weightings option on (i.e. set to "Y").
The 20 game-stats sheets are where the totals for each game are entered. Placing/score/kills/assists/deaths are entered straight from the details on the Post Game Carnage Report. If a team game was played: enter a "Y" in the "Team Win?" column if they player was on the winning team and an "N" if they were on the losing team. The type of game & map can be entered in the green "Type:" area, and any Team Scores entered in the "Blue:" & "Red:" areas.
The "Teamwork Analysis" sheet is for viewing only. It has a cross-reference listing of the team games won & lost, with each other player.
The "Team Balancer" sheet is used to calculate 2 evenly-matched teams. Enter a "Y" in column C for each player you want to include in the calculation, and then click on the "Calc" button. There is also a "Man Down adjustment?" variable you can set to "Y" or "N". This is only used when calculating for an odd number of players. We recommend setting this to "Y" for King of the Hill & CTF games, and "N" for Slayer games.

Technical Details:

Using the spreadsheet: The spreadsheet is in Excel 2000 format. In order to allow the Team Balance function to work, when you open the spreadsheet you need to click on the "Enable Macros" button. Also, you may need to activate one of the add-ins in Excel (you only need to do this once). The Team Balancer uses functions which are in Excel's Analysis ToolPak. To activate this, open the spreadsheet, click on "Tools" on the menu bar and select "Add-Ins". The "Add-Ins" window should appear. Select "Analysis ToolPak" from the list (put a tick in the box) and click OK.
Team Win Weightings: The idea of team win weightings, is to recognise a player's contribution to helping their team win. For example, you may have kept the heat off your buddies in the hill, or escourted a flag-carrying team-mate home - but all you get for your trouble is a bad overall placing, while your team-mate comes 1st and gets all the glory. Team win weightings help to balance this out. When team win weightings are used, everyone on the winning team has their placing adjusted downward (i.e. improved). The size of the adjustment is proportional to the size of your team. For a 3-man team, each player's contribution to the team win is 1/3 (and each player's placing will be reduced by 1/3). For a 5-man team it will be reduced by 1/5. This means that if you come 4th place on a winning 4-man team, your team weighted placing will be 3.
Kill/Death Ratio: The K/D ratio gives an idea as to what end of the "food chain" the player is on. It's more useful than total kills etc., as it is independent of how many games each player has played. If it's less than 1, they're dying more often than killing. Greater than 1 means that they're giving more than they get. It is calculated using the number of kills, assists & deaths. Assists count as half points for kills. The formula is: K/D = (Kills + (Assists * 0.5)) / Deaths.
Weighted Score: Weighted Score is used to consolidate all the player stats into one number. It's this number, that is used to rank the players - and determines who is the overall winner. As with K/D ratio, it is independent of how many games each player participated in. The formula has been fine-tuned to give proper weighting to achieving team objectives, as well as to reward good K/D stats. It is calculated as WS = (100 / Avg (TW) placing) * sqrt(K/D).
Man Down Adjustment (MDA): This is used on the Team Balancer page. It is only applicacble when calculating for an odd number of players (i.e. where one team will be one man short). Usually, having 2 teams of near-equal skill levels is enough to ensure a balanced game. In some games types, however, having an extra person on one team can be a distinct advantage. In games such as King of the Hill & Capture the Flag - having an extra person to stand in the hill or guard that flag, can produce a strategic handicap for the lesser team. The effect of using the MDA, is to make the team with fewer players an overall stronger team. The size of the adjustment depends on the size of the larger team - and is calculated as 100 / team-size. If the larger team has 4 people on it, the MDA will be 25%, if it has 6 people, it will be 16.67%. So... if you are calculating teams for 11 players with the MDA on, the 5-man team will have its accumulated skill level reduced by 16.67% before trying to balance it against the 6-man team's accumulated skill level.

We are NOT Bungie, Inc.! Bungie Inc. are bungie.net!
Halo © Bungie Inc., 1999-2012, Microsoft 2012-2024
Intellectual © halo.bungie.org, 1999-2024