I would like to work out the probability of a goal being scored

You have done some great work on that sheet but now you need to spend a lot more time formatting that Data into some way of looking up each individual combination of scores.

You will need
1) A fixture list for the 2019-20 season. Copy and paste into a new tab. Flashscores.com usually has the best format for Excel**.

2) Using INDEX MATCH in 2 extra columns and next to each fixture you can look through your data to find the average for the home team and the Away team for that Game.

Home Team:

Please Login or Register  to view this content.

Away Team:

Please Login or Register  to view this content.

3) Use a basic nested IF Formula to determine which team will score the most goals.

**Things to look out for, the web list of names maybe different than the team names you have, Newcastle Utd, instead of Newcastle, or maybe an extra space infront of or behind the team names which will show up as a blank in the INDEX MATCHES. You will need to work around these if you find them.

There is an example in the worksheet in Sheet 1.

PS: ALL London teams suck :-)

The example I gave you in the return template should be extended to pick up anything you would like to take into consideration, then you can choose which percentage is more likely to happen. ie: if Arsenal are more likely to score 2 goals at home against a Southampton team that may score 1 goal then that should give you a prediction for THAT game 2-1.

The issue I had with this method is that almost every teams average at home is higher than the away team which gives off a scewed result of the home team winning every game. The top 6 for sure, the rest of the league are almost all the same versus each other, scoring an average home or away of 1 to 2 gpg. You can work out the strength of a team by averaging out for each season how many goals they score at home pg, then average out how many the away team score against them to give the defensive strength. The attacking strength is the average home goals minus the average away goals, this figure can then be used to determine the best outcome against every opponent.

When you start adding the actual scores (after the season starts) and check this against your predictions then you begin to see what the problem is. Two seasons is not enough data, seven may be too many, five seasons worth of stats seems to be the norm.

I'll try and work on your template to show you what I mean.

In the meantime use your new found Web Data Query skills to pull in the past FIVE seasons fixture listings complete with scores. Once you have this data, select all of it, Select Copy and then Paste Values. Remove the Data Links. Use the FIND & SELECT Ribbon to replace the relegated teams with the promoted teams.
(Not as easy as you think :-) )

To help other people reading this post this is how to get a Web Data Query started.

Navigate to a site that looks to be suitable for transferring into Excel. Copy the URL.

Back in Excel, select a new tab.

Select Data, From Web.
A message box will open up. In the 'Address' box paste the URL you copied. Click GO.
After a short while the data from the website will begin to show up. Once that has finished hopefully you will see yellow boxes with black arrows next to the data you wish to import.
If not you will need to find another site. (Not every site is Excel friendly).
Click the arrow next to the data and then click 'Import'.
Select a blank cell where you would like the data to start.
Wait for the data to be imported.