After loads of modifications to make it more userfriendly and easier to edit, I finally got my stat gathering sheet ready for general use, and thus I preset:
The dictators 1.32 Arena StatsheetTable of Contents- Introduction
- The Sheet
- The Input
- The Output
- Thanks and Sources
- Q & A
- Tips and Tricks
- Tl:dr Version
- To-do List
- Changelog
IntroductionFor most of the games I play, at least when it takes longer than a few minutes, I end up developing my own quick calculation tools in OpenOffice or Excel (depending on whatever is installed on my computer at that time), at least if I can't find satisfactory tools somewhere on the internet (and even those usually end up modified for my own personal preferences). Those tools usually don't have a very high standard on layout or user-friendliness, since new functionalities get added whenever I feel the need to, and wherever I can find room, thus when antiaverage showed interest into the tool, it took me a while to modify it into something everyone can (hopefully) understand.
Stats acronym glossary:Spoiler for Glossary:
TTW: Turns To Win
TTL: Turns to Lose
EM: Elemental Mastery (winning with 100% of your max hp's)
UEI: Unified Efficiency Index (approximate electrum/hour assuming all spun cards are sold)
SSPH: Special Spins/Hour
For more details on the formula used for UEI and SSPH, see the
UEI thread.
In the google document, there will be a sheet named Template, which is protected from editing to keep it clean. Whenever you want to test a new deck, you can simply duplicate this sheet to get a clean sheet, fresh for testing a new deck (but first check if someone else has already tested said deck, you can collect your stats on the same sheet, they will be automatically pooled together, and it reduces clutter, see the next paragraph). Ideally there will be 1 sheet for (each version of) each deck for each arena, and looking at the FGei file, this system seems to be working well.
Each sheet has a couple of parts to it (see the screenshot):
- The Deckinfo (Green Cells)
- The Data (light grey cells)
- The forum code output cells (dark grey cells)
- The formula cells (white cells)
- And finally the headers and on sheet output (black cells)
All these different areas will be explained in the next 2 sections, but I will put a small note here: The Forum Code and Formula area's are all protected cells, to prevent people from messing up [or with] the formulas, reducing my workload in fixing them again. If you really think they should be changed, ask me to do it (or try to get me to grant you editing rights). Part of the formula cells (the columns O to X) are hidden, again to remove clutter (and they are protected anyway). The 9th row of each sheet lists the totals of all sessions, I left this one visible, because people might be interested in those (like when they want to stop at 200 total testgames).
The InputThere are 2 main input areas and they can be seperated into 3 types:
- One time input (The deckinfo and the arena selector)
- Once per sessions (Tester Name, Date, Reward)
- Every game (The other columns)
One time inputAssuming you made an awesome new deck, and want to gather some stats so you can convince other people it's indeed awesome, you first start by making your own sheet (explained in the sheet section). After that, you will have to add in the following data once:
- The name of the deck
- The deckcode
- The creator of the original deck (author)
- Modified by - If you or someone else modified the original deck (this NT rush works much better with a few ice shields added in), while preserving the general idea of the deck, you can input that persons (forum) name here.
- A link to the forum thread of the deck. Not really needed at the start (maybe you want some stats first, before making a post about it), but the arena study by ColorlessGreen requires a deck thread. If you only modified a deck, feel free to link to the original one.
- What arena league you are testing against. I will say it to prevent confusion, one sheet per deck PER arena, or some of the formulas break (I will probably say it a few more times in this thread)
Congratulations, you can now start your testing, none of the one time input data is required except for the league selection, but all of them make the format look much better (and they save ColorlessGreen the trouble of having to look for it himself if you want to post your data in the arena study thread), but you can fill them out after collecting some initial stats, to see if your deck is indeed as awesome as you thought it was (maybe you realize after some tests that the deck needs another ice shield).
Once per sessionFirst, I think I will have to explain session a bit. With session I basically mean a number of stats collected closely after each other, say in the same few days. If you test only every Saturday evening, start a new session each time, the arena is a fast changing meta, and it helps understand your stats if people can see all testing was done on Christmas, when there were lots of farms in the arena. (If you need more rows, as me or someone else with editing rights to add some).
Every session you move to the next column, and insert your forum name, the date (don't worry to much if the testing happens on 2 or 3 consecutive dates, just pick one), and the reward for that arena at the time (usually 12 for bronze, between 55 and 60 for silver, between 120 and 130 for gold, and around 250 for platinum league). The reward is optional, but more on that in the next subsection.
Every gameThere are (of course) also a number of stats that need to be collected after every game (or rather, whenever they change, which, for most of them, won't be every game).
- Wins
- Total TTW
- Electrum (No EM)
- EMs
- Electrum (EM)
- Losses
- Total TTL
- Special Spins
- Cards
- Rewards
Before explaining the individual ones, I'll first explain the different header colors.
As you might have noticed, there are 4 different colors: Red, Orange, Blue and Green (and white, but white doesn't have a special meaning).
Red headers are for necessary stats: Wins, Total TTW, EMs and Losses
Wins is the total number of wins this session. Every time you win a game, simply increase the number by 1. In the same way, EMs and Losses list the total numbers of EMs and Losses, respectively. (Note: Wins includes EMs, thus every EM should both increase the Win counter and the EM counter by 1).
Total TTW (turns to win) is also listed as a mandatory stat, for 2 reasons. First: Without it, you don't need such a complicated spreadsheet to collect your stats. Pen and paper should do fine. And second: It is (like the other 3) a requirement for the advanced stats UEI and SSPH.
This one requires a bit of practice: after you win a game, the number of turns will be listed in the top right corner. The first time you enter this number, place a '=' in front of it. After that, you can use the f2 function key to quickly jump to the end of the formula (after selecting the cell), and put down '+' and whatever the TTW for that game was. This way, you can let the spreadsheet do the calculations, instead of having to do them yourself. (Though, if you prefer, using a calculator and a head, and just putting down a number every time works fine, but it's probably more sensitive to typo's/copying errors).
Total TTL (turns to lose) works just like Total TTW as far as inputting goes, but is an 'orange' stat. I chose a seperate color, because it isn't used in any of the calculations, except for average TTL and average game duration. This is because it is too subjective: one person might quit if he sees he will lose the game after a few turns, while another might play until the end, hoping for a 'miracle'. Therefore lost game durations can vary wildly between different people, and thus aren't the best method to obtain objective statistics.
I still listed them as orange, because they can be nice comparison material between testers (and between different decks by the same tester), and because the output looks bad without them
The Blue stats are the electrum related stats, but in future versions, those can also be used to calculate remaining HP's and Score gains. Not required for the arena study (neither are the green ones, btw), but it's always nice to see you made 3k testing your deck against platinum league, and if hp or score stats are ever implemented, they could be immediately calculated for decks that have collected numbers in those columns. Reward is a once per session stat, and ideally, you should start a new session whenever the reward changes, though that is not important right now, because it's only reported back, not used in any calculations at the moment (but it would be required for remaining hps and score gain calculations). The other two [Electrum (No EM) and Electrum (EM)] are totals, and are best collected like the total TTW.
The Green stats aren't mandatory for anything but nice to know (and brag about). A simple number, updated every time it changes, should do fine here.
The OutputThe output can be separated into two types: the numbers in the sheet itself, and the forum code cells where you can find code that can be copied into a forum post.
The sheet itself lists Winrate, EMrate and TTW as basic stats (those are over the total of games, not the current session), and UEI and SSPH as advanced stats.
The Forum Code Export cells (gray background), can be used as simple copy paste of your data, to report them on forums, in the arena study or in the decks thread.
There are two types here: to the left of every session, there is a forum code function that can be used to export the results of that session. Currently, as one of the annoying side effects of using google docs, copying out of cells sometimes adds characters: copying out of the Deck Code cell and Forum Export Code currently leaves a number of tabs and line breaks after the contents (in the case of the deckcode, this means the mark won't be imported properly), and all forum export cells generate quotation marks around the text when pasting into anything that isn't google docs, unless someone knows a way to fix this, people will have to fix this manually. They will be formatted like this (without the spoiler):
Spoiler for Session stats forum code:
NT Rush*Deck*, by
furballdn, modified by
the dictator, tested by
The dictatorTested against
Bronze, on
4-jan-2013.
General | Speed |  Electrum | Other |
| Wins: | 76 | 76% | | Losses: | 24 | 24% | | EM: | 3 | 3,95% |
| | TTW: | 9,45 | | TTL: | 12 | | Average | 10,06 |
| | per Win (non-EM) | 8,89 | | per EM | 24 | | Total Net Gain | 361 |
| | Special Spins | 5 | | Normal Cards Won | 30 | | Rares Won | 5 |
|
Notes:
Tests were conducted when Reward was 12 and EM reward was 24.
Tests were done and forum output was generated with help of the
1.32 arena statsheet.
Some of the numbers break when certain stats aren't filled out, and there won't be any code generated if that session doesn't have anything filled out in the win column.
Other parts simply won't be displayed if certain parts are left blank:
- Modified By - Won't be displayed if J3 is left blank
- Date - Won't be displayed if there is no date listed for the session
- TTL, Average Speed - Will show 'No TTL', if no TTL has been recorded
- Electrum table column - won't be displayed if no Electrum (No EM) value has been recorded
- 'Other' column - Each of the individual stats will be left out if their cells are left blank. If all are left blank, no column will be made.
- The note about the reward will be left out if no reward is listed.
And the deck summary is formatted like this:
Spoiler for Deck total stats:
Summary of tests with this deckGeneral | Speed | Additional |
| Wins: | 76 | 76% | | Losses: | 24 | 24% | | EM: | 3 | 3,95% |
| | TTW: | 9,45 | | TTL: | 12 | | Average: | 10,06 |
| |
Tests done by
The dictator.
Tests were done and forum output was generated with help of the
1.32 arena statsheet.
The only conditional stats here are the TTL and average speed numbers: they will display 'No TTL' if even one of the sessions is missing TTL data.
Thanks and SourcesSpoiler for Thanks to:
ColorlessGreen for running the arena study that motivated me to collect stats in the first place, and for making, and helping out with some of the formulas.
Pella for creating the newest FGei study, both the thread and the spreadsheet I used as inspiration
Dm1321 for proofreading, and catching all my typo's, broken sentences and grammatical errors (at least, I hope he caught them all
)
Shantu for his unofficial betatest, and (more importantly) nice feedback afterwards.
Everyone for their interest and their persistence to read all the way to here, and for whatever you did that I forgot about.
