Plotting the Results of Your Expected Goals Simulation.

Ok, you've run your simulation. If you haven't, check back to this previous post.

This time you've looked at the 49 non penalty goal attempts of Stoke's five goal Jonathan Walters from the same season as Bale's phenomenal 20+ goal season. We've generated results from 1,000 iterations of those 49 attempts.

We're likely to be interested in how many of the 1000 iterations resulted in an average Premier League player scoring a particular number of goals based on the model from which the goal expectations are derived. In column "I" I've counted up from zero and used the "countif" function to record how many times particular number of goals were scored in the 1,000 iterations.

The total goals scored in each simulation are totaled in the range G2 to G1,000 and that is the range we use in the countif function in the screen shot above. Add the $ signs to anchor the range when you paste down in column J.

The criteria for our countif function is the number of goals which we've put in column I. So I2 tells it to count how often zero goals were scored in the simulated seasons. There's no need to include the $ sign this time because we don't want the criteria to be fixed.

While the raw frequency of likely goals scored in the simulations does tell us something useful, a percentage of the whole is a more useful output.

Insert a column between the "Number of Goals" and "Frequency" columns and sum the number of iterations. It's in cell K19 and in this case is 1,000. Then divide the individual frequencies by this total number of trials. This is done in column "J". Again make $K$19 an absolute cell with the $ signs before copying down from J2.

The number can be expressed as a percentage by using the "%" from the "Home" tab. Additional decimal places can be added with the two buttons to the right of the "%" should you wish.

To add the graph, first highlight the range that contains the data. In this case it runs from K17 to J2. Click "Insert" on the ribbon and then "Recommended Charts".

A variety of choices will be suggested, but the histogram works best, so click to chose that option.

News Reaches Super Jon of Bale's Amazing Conversion Rate.

You can add labels in your own editing software of choice.

A slight probable under performance from Walters in 2012/13, scoring five non penalty goals from his 49 chances. However, it's around a 30% chance that an average Premier League players does as good or worse than Walters' actual record, so nothing too much to worry about given that Stoke weren't prolific chance creators.

It possibly explains why Real Madrid were in for Bale rather than Super Jon, though.