Running a Simple Simulation With Excel.

A post on how to run your own simulations in everyone's starter package, excel.

Requirement one. Your own expected goals estimates for each shot/header taken by the team/player and a full version of excel.

In column A you've got Bale, B has the expected goals for each non penalty attempt (he had 166 in his final Premier League season), C has a randomly generated number (=rand()) and D defines whether or not a goal was scored. You can see how it decides in the formula bar in the screen shot above. (=if(C2<B2,1,0)

 Sum the number of goals scored in this single iteration. It's in cell E167 and the formula is in the fx bar (=sum(D2:D167))

Copy the total goals scored in this one iteration into another cell to record the result. In this example I've placed it in H2. We're going to want to run lots of simulations, so in G2 I've started counting up from one, ending in G1001 with 1000. We're doing 1,000 runs in this example.

Now we want to run 1,000 iterations or more if you wish. So having copied G2 to G1001 from 1 to 1000, highlight cells H1001 to G2 by dragging the mouse from bottom right of the range to top left.

Next we need to click the "Data" tab on the ribbon and chose "What-If  Analysis" and finally "Data Table" on the drop down menu.

Once you've clicked on "Data Table" this action box will appear. By default the cursor is in the "Row input cell", but we need the "Column input cell". So click the cursor into this box.

Nearly there. Once you've got the cursor flashing in the column input cell, simply click on any cell without data. I've chosen B1001. Click "OK" in the "Data Table" box and the results of 1,000 simulations should with a bit of patience auto-fill into the cells from H3 to H1001.

To re run the 1,000 simulations, simply click on the "Formulas" on the ribbon, click "Calculations Options" and re check "Automatic" on the drop down menu.

It's then just a case of counting the frequency of each possible goal total, plotting a few graphs and posting an article.

Bale fails to add to his 2012/13 goal tally in his penultimate Premier League appearance.

Here's a chart of the frequency with which an average Premier League player would score a particular number of goals from Bale's 166 chances during 2012/13 just before his big money move to Spain. Bale scored over 20!

@SteMC74 has done sims using R here, but I can't find any post that just use excel.