[Exclusive] Inside The Top Ten Spreadsheet

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.

Last week’s Top Ten results output (Click to Enlarge)

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.

Sample input data from the Top Ten two weeks ago (Click to Enlarge)

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.

The Top Ten Armies macro button along with directions for those completing it

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.


My E-Lesson:




18 Responses

  1. >algorithms
    1st year programming class much

    • Logarithms, not algorithms. Algorithms are the same thing as formulas. Logarithms are basically inverse exponential equations most often used for regression, one of which was used to represent the “Ausia curve” (it’s mathematics, not programming).

      • Misread. Sorry m8.

        • No worries, your comments are still more intelligent than 95% of the comments I usually respond to

  2. seems complicated, glad i dont have to do this

    • remember when top tens were just the makers opinion

      pepperidge Funks remembers

      • SMAC still does that lenco.

        • you ruined my meme

          • *cries*
            I didn’t mean to.

            • your sins are forgiven son

              • yippee

  3. Thanks boomer, but now I feel bad because you made this post right after I asked about it…

    • I actually made it like half an hour before you asked so I assumed you asked because of the post xP

  4. Oh a post that could help leaders make the perfect plan to get 1st? NAH TOO BUSY READING THE PERSON OF THE YEAR POST

    • Too busy complaining about not being on the person of the year post*

      • butthurt much

  5. Owow

  6. o wow

What do YOU think? Comment your opinion!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: