Hi,
I have a general interest in sports and to cut a long story short did a regression (using Data Analysis in Excel) to predict goals in a season for teams based on 10 years of English Premier League data based off of various factors (I wanted to find out from the data that I had, what made a goal). In short the formula I came up with is in cell B6 of the attached as I wanted to see if this worked for players. As a result I took the data for Robin Van Persie and stuck it in B3 and B7 and adjusted the games down to 36 (I took this two games before the end of the season). I am of the impression that the predicted way of doing things (as per B6) is generally not the best way of doing things as it doesn't allow for the random factor hence;
I assumed goals were a Poisson Random Variable and for simplicity stuck this to 3 decimal places (as nobody typically scores 5 goals in a game). I generated a data table on this basis based on the random number in A27 and the formula in B29 (incidentally is there a cleaner way of doing the formula in B29?).
In short based on 5000 games played this leads me to the table in the middle with a seasonal average in F36 (which seems to vary between 28.8 and 30.8 - Van Persie scored 30 making the data table simulation better than the 27.28 prediction in B6 - validating the random factor concept).
Here's the question - I've simulated 5000 games based on this (data table on the left hand side), can I simulate 5000 seasons based on the information that I've got (e.g. the data table on the right hand side - I've set it up based on the result of F36 but this obviously won't work as it only has one result - based on the average of the 5000 games data table). I have set up an average in F38 for if I can figure out the season data table. My logic being if I simulated 5000 seasons, the prediction of goals per season should stabalise rather than varying by about 2 (in this case based on the Van Persie data). Also if anyone has a more elegant way of doing B29 I would greatly appreciate some feedback.
Thanks in advance.
Goal Workup Idea 5000 Trials_RevA.xls
Bookmarks