Just to be clear, I am a bit of a nerd. Not exactly BBT level, but I can completely relate. I am socially inept, but I can fake it to make it. I find formulas and math and statistics interesting (FYI, stats is not math even though it tends to be lumped in with math in most institutions) even when I don't fully understand it all. I like playing with spreadsheets. I wish I knew how to write VBA, and I think I can maybe learn it, but I don't really have time and I truly suck at coding of any sort.
My system is self-calculating in that all I do is download results from Sports Reference and paste them into a specific tab. The only manual manipulation involved is removing their rankings numbers so that my formulas are looking at clean team names.
What is an iterative calculation? Glad you asked! Putting it as simply as I can:
Team A is rated some # based on the ratings of teams they beat and teams they lose to. In the Simple Ranking System, it's all about scores. So, Team A is rated a number based on the rating of Team B whose number is based on Team A and so on. Now do that with 130 CFB D1 schools times 12 games. Every time you do the calculation, the numbers change and you have to do the calculation again. If you don't set some parameters, the calculations never stop.
You can find a much more detailed explanation here.
In a spreadsheet, you will get an error regarding a circular calculation in your formula if you try it unless you turn on iterative calculations. When you turn this on, you set the rules for how many times it will calculate based on a set size of change, or how many iterations of the calculations will there be. So I can set the sheet to calculate n number of times but only if the change between calculations is greater than x. My choices are the values of n and x. I must consider the numbers I'm working with (how many decimal places) and the system's capabilities (how fast can the sheet do the calculations).
If you do not perform enough calculations, it is possible that your numbers will not have settled down enough to stop changing rankings. You may have noted that my system generally operates on 4 decimal places (0.0000). If I tell the sheet to calculate 5 times, it is highly likely that the fourth, third, or even second decimal places will still be swinging wildly high to low and back. If I tell it to calculate 1 million times, Google sheets might actually break and we'll be waiting for the end of time to get a result. Also, if I set the calculation threshold to, say 0.1, then any calculation that results in a change less than that will stop the iterations. If I set the threshold too small, it may make irrelevant calculations and slow down the process too much.
What is the answer? There really isn't one hard and fast rule. For myself, I read a blog post from a guy who set up his sheet to calculate the SRS linked above. I then looked at my system and decided that the threshold should be 0.001 because anything smaller than that is unlikely to make enough of a difference to rankings. I set the number of iterations to 300 based on some trial and error, testing between 100 and 10,000. Google sheets just does not calculate very quickly with the amount of data I feed it. 300 iterations at 0.001 variance appears to work well, and sometimes I do change it to 1,000 at 0.0001 just to see if there are changes.
So, why will iterative calculations be the death of me? My sheet has 2 completely different yet interrelated sets of iterative calculations, and early in the season the swings are crazy big. Sometimes they break the sheet, and then I have to try to track down why I'm getting the dreaded #N/A or #DIV0 error in so many cells. Sometimes the calculations take so long I just hide the window and go do something else, and many times when I get back the results make no sense.
What's the answer? I am finding that I need to set the number of calculations much higher this season after adding my method of measuring for good losses and bad wins, which I will explain when I release my real rankings in a few weeks.
No comments:
Post a Comment