3 replies to this topic

[TheStrider]

Hi all, around December when I was stuck in a hole matchmaker wise, I created myself an Excel spreadsheet that interfaced with the Forums Stats page.

It has grown since my original idea, I figured I'd share it with everyone now that I've got the calculations a bit more automated.

This sheet will calculate:

• Overall Kills, Deaths and Ratio
  
• Overall Wins, Losses and Ratio
  
• Overall Total and Average Damage Dealt per Match
  
• Time Played (!)
  
• Weight Class Wins/Losses/Kills/Deaths and appropriate Ratios and Average Damage

It is available here on my Dropbox.


How to use it:

1 - First go in and highlight all your mech stats from here. Copy those numbers into your clipboard.

2 - Paste said numbers into A2. There is room for 100 rows at the moment. If you have more than 100 mechs, you must add more rows. If you need more rows, you must insert them BEFORE you paste, otherwise the formulas will corrupt.

3 - After pasting, fill in Column M with the weight of each mech. You must use "Light" "Medium" "Heavy" "Assault" to fill this in with. The formulas select off these words.

4 - If you have the Phoenix Package, you will need to adjust the formulaes there to match where in your list your Phoenix mechs are.

5 - WHEN YOU BUY A NEW MECH CHASSIS: Insert a blank line from Column A to N before you re-paste your stats, and it will adjust properly.


Hopefully I explained all this correctly. Any questions, ask. If you have suggestions for other things that could be done, make them. If you have a better grasp of Excel, feel free to suggest improvements, as despite being an IT guy, I don't spend much time using the thing.


PS: For those who were around since before they implemented the current stats sheet, there is two sections that allow you to back calculate your Old stats. Visit the main stats page and take the difference in those numbers and you can back calculate your prior kills, deaths, wins and losses. Put those numbers in the "Old Games" etc fields. Or ignore this section and not care.

Edited by TheStrider, 02 March 2014 - 08:44 AM.

[loopala]

nice work.

BTW it works in open office 4 with out a problem

[TheStrider]

Excellent, I figured it should, but hadn't tested it.

[stjobe]

Nice.

If anyone else but me uses Google Drive (or Google Spreadsheet), there are no COUNTIFS, SUMIFS, or AVERAGEIFS functions, but you can translate them rather easily:

Replace '=COUNTIFS($M$3:$M$103, "Light")'
with '=COUNTIF($M$3:$M$103, "Light")'.

Replace '=SUMIFS($C$3:$C$103,$M$3:$M$103, "Light")'
with '=SUM(FILTER($C$3:$C$103;$M$3:$M$103="Light"))'

Replace '=AVERAGEIFS($N$3:$N$103, $M$3:$M$103, "Light")'
with '=AVERAGE(FILTER($N$3:$N$103; $M$3:$M$103="Light"))'

And so on (Please notice that the first comma changes to a semicolon and the second to an equal sign, and don't forget to add the last closing parenthesis).

After that it works splendidly.

Good work!

Edited by stjobe, 10 March 2014 - 01:15 PM.