Hi,
I was hoping someone can help me with a convergence problem. I am attempting to replicate the following online video that details how to calculate a simple rating system for football teams: Excel Iterative Tutorial - Sports-Reference's library
However, I cannot get the formulas to converge on an answer when I turn on iterations.
To summarize, I have calcualted a margin of error for a number of football games (first 7 weeks in 2009) (first sheet, column I), then created averages of those margins for every team using a simple array formula (second sheet, column B).
The simple rating system score (SRS) is a sum of the average margin of victory plus the average of your opponents SRS. As you can see, this involves multiple formulas that need to converge. First sheet, column J has the numbers that should be the SRS of each of the opponents, but right now is a VLOOKUP to their average margin of victory so that it is not constantly iterating (I search and replace ,2 to ,4 to make it look at the SRS column on the second sheet to begin the calculations).
You can download my small workbook (no macros) here, since this may not be the clearest explanation: https://drive.google.com/file/d/0Bzr6jaapQdMRUi11OTFOV1B1Tkk/view?usp=sharing
When I begin the calculations, it runs through however many thousands of iterations I set as the limit, but it does not converge, just keeps pushing all of the numbers up and up. In the video, it seems to converge pretty quickly. I have searched for answers and have not found any guidance on this type of problem.
Any help or guidance would be much appreciated!
Thanks.
-D
I was hoping someone can help me with a convergence problem. I am attempting to replicate the following online video that details how to calculate a simple rating system for football teams: Excel Iterative Tutorial - Sports-Reference's library
However, I cannot get the formulas to converge on an answer when I turn on iterations.
To summarize, I have calcualted a margin of error for a number of football games (first 7 weeks in 2009) (first sheet, column I), then created averages of those margins for every team using a simple array formula (second sheet, column B).
The simple rating system score (SRS) is a sum of the average margin of victory plus the average of your opponents SRS. As you can see, this involves multiple formulas that need to converge. First sheet, column J has the numbers that should be the SRS of each of the opponents, but right now is a VLOOKUP to their average margin of victory so that it is not constantly iterating (I search and replace ,2 to ,4 to make it look at the SRS column on the second sheet to begin the calculations).
You can download my small workbook (no macros) here, since this may not be the clearest explanation: https://drive.google.com/file/d/0Bzr6jaapQdMRUi11OTFOV1B1Tkk/view?usp=sharing
When I begin the calculations, it runs through however many thousands of iterations I set as the limit, but it does not converge, just keeps pushing all of the numbers up and up. In the video, it seems to converge pretty quickly. I have searched for answers and have not found any guidance on this type of problem.
Any help or guidance would be much appreciated!
Thanks.
-D
