In March of this year, CPAC was faced with criticism over its Top Ten formula, as many armies began to abuse it in ways which had not occurred previously, leading to numerous controversial placements for the armies involved. As a result, I set to work creating the formula we now use for our Top Tens. Seeing that the resulting formula was significantly more intricate, and in empathizing with those poor souls who would now have to calculate it, I created a macro-enabled spreadsheet to make the process smoother, more efficient, and with lower risk of error.
Documented below is the first real inside scoop into the processor behind the Top Ten.
For the vast majority of viewers, the extent of this project that has been seen is the output generated by the sheet, offered in image-form with every Top Ten and displaying which categories each army received their points from. I would venture to say that many if not all of the people who have actually used the sheet don’t actually know how it runs. They simply press a button – the beauty that is an Excel macro.
The spreadsheet is broken into two tabs, one which contains the “inputs”, which are the metrics like average sizes and number of events, and the “outputs”, which are the total points assigned to each army based on the formula. The people who do the Top Tens enter the inputs into the sheet, press a button, and voilà, the Top Ten completes itself. Both CPAC and SMAP have been provided their own unique copies of the sheet, as both have slightly different formulas.
As you can see, there are a wide range of inputs, including average size broken down by division, number of events for each division, tactical points, a breakdown of what type of events were held, and finally the consistency metric.
After the Top Ten Armies button is pressed, the sheet runs a script behind the scenes that makes all of the necessary calculations (some of which are fairly complicated – logarithms!) and formatting adjustments to generate the final output you see in the Top Ten each week. I have included a video below (full screen recommended).
As you can see, the sheet processes for a few seconds before spitting out the completed Top Ten data.
One thing that can’t be seen in this video is what the macro script is actually doing. I have included another video below that shows the process step-by-step (full screen recommended).
The first thing the macro does is sort the output data by Total Points, effectively “ranking” the armies in the list. It then creates a new column where it generates the ranking numbers. Lastly, it begins for format the output data for publication, including adding gridlines, adding a green color coding to those armies that made the Top Ten, changing the text to white and bolding those that made it, and resizing the columns.
The last video I have created for you shows the actual scripting behind this magical button macro. The idea of a macro is to do the hard coding work one time so that every other time becomes as easy as the press of a button. Excel macros are coded in VBA, or Visual Basic for Applications, and for any who have some knowledge of it, you may recognize the pieces of the script that are completing the functions I described above as they are executed (full screen recommended).
I hope you enjoyed this inside look at what makes the Top Ten tick. As always, the people at CPAC are committed to providing the best representation of armies today, with special emphasis on the Top Ten, as we know the value that it holds to leaders and soldiers across the community.
For those curious about Excel, I highly recommend poking around in it and maybe looking into a few basic tutorials online. It is a very useful piece of software that is used in a wide variety of occupations all over the world, especially if any of you are aspiring businessmen/businesswomen. On the off chance anyone is interested, I have embedded a pair of tutorial playlists below.
Filed under: Uncategorized |