Building a Poisson Probability Football Excel Betting Model – (Part III)

Building a Poisson Probability Football Betting Model – (Part III)

Building the Excel Betting Model Calculations

The Excel Betting Model structure is now in place.  The 3 tabs have all the headers and the team names in place.  If you need to check back at any point, you can do so here

How to Build a Football Betting Model
Building a Poisson Probability Football Betting Model – (Part I)
Building a Poisson Probability Football Betting Model – (Part II)

There will be a fair bit of jumping around between the two spreadsheets below as we start off, but it will all make sense.

Poisson Probability Football Betting Model - Name your Tabs

Poisson Probability Football Excel Betting Model – Name your Tabs

Building the code – Premier League & Stats UK – POI Tabs

Select the ‘Premier League’ Tab.

Entering Fixture Information

What needs to be done first, is to enter the first 2 weeks fixtures for your chosen division.

The reason we need to enter a couple of weeks of fixtures is, that otherwise all the formulas would be blank or error.  Also, they way this model works is that the top set of fixtures (current at the time) are not included in the models calculations until they are finished.   It analyses the numbers for everything after row 15.

Poisson Probability Football Betting Model - Name your Columns

Poisson Probability Football Betting Model – Entering the fixtures.

In the Columns “Home Team” and “Away Team” fill them out with the first two rounds of fixtures.  Leaving a 1 row gap between each week.

Enter Week 2 First, then a space, then week 1.

Now move all the way to the left, to the first Column called Fixture

For the purpose of my sample below

FK = Home Team Column

GE = Away Team Column

Enter the following code

=FK4&”:”&GE4

It should display

Bournemouth:Watford

How to build a Football Betting Model - Fixture Details

How to build a Football Betting Model – Fixture Details

Once completed your first two weeks of fixtures should have been entered and column A (Fixtures) should have been coded.

Select Column B (Week)

Select the first fixture of week 1 (row 15)  enter 1

In the next row enter

=B15

Copy and paste this down for the rest of the Week 1 fixtures

Select B4

enter =B15+1

In the next row enter =B4

Copy and paste this down for the rest of the Week 2 fixtures

You should now have Weeks 1 and 2 labeled appropriately.

Filling in the Stats UK – POI tab

Select the Stats UK – POI tab.

Games Played

This is the calculation for the “Pld” Column in the “Home” section

The Code you are going to enter is this, but you need to do it in steps.  So we’ll break it down

=SUMPRODUCT((‘Premier League’!FK$15:FK$1044=’Stats UK – POI’!A4)*1)

NB:Columns marked FK in our example should be replaced with the column that displays the home team in the Premier League tab on your spreadsheet

Buiding a Football Model - Fixture Count (Home Team)

Buiding a Football Model – Fixture Count (Home Team)

Select Cell B4

Enter

=SUMPRODUCT((

Now, without pressing enter, click the Premier League tab and select cell FK15 (Home Team) and drag down to cell 20

then press =

Click back on the Stats UK – POI tab and select cell A4

now add )*1) and press enter

Your code will look something like this.

=SUMPRODUCT((‘Premier League’!FK15:FK20=’Stats UK – POI’!A4)*1)

In between FK (Home Team Column and 15 add a $   Same after the 2nd FK

Delete 20 and enter 1044

=SUMPRODUCT((‘Premier League’!FK$15:FK$1044=’Stats UK – POI’!A4)*1)

The Cell should now be showing (As per image above)

Copy and paste for the other 19 home teams

Once completed, select Cell AX4

This is the calculation for the “Pld” Column in the “Away” section

The Code you are going to enter is almost exactly the same just changing the reference cells for the Home Team to Away team in the Premier League Tab.

=SUMPRODUCT((‘Premier League’!GE$15:GE$1044=’Stats UK – POI’!A4)*1)

NB:Columns marked GE in our example should be replaced with the column that displays the Away team in the Premier League tab on your spreadsheet

The Cell should now be showing 0

This is because you only have 2 weeks of data entered, and only 1

Buiding a Football Model - Fixture Count (Away Team)

Buiding a Football Model – Fixture Count (Away Team)

Games Won

We now have the number of home games and away games played calculated, so now from those games we need to calculate how many have been won.

This is the calculation we are going to enter

=SUMPRODUCT((‘Premier League’!FK$15:FK$1044=A4)*((‘Premier League’!$FF$15:$FF$1044=’Stats UK – POI’!$C$3)))

NB:Columns marked FK in our example should be replaced with the column that displays the Home team in the Premier League tab on your spreadsheet
“Columns marked FF in our example should be replaced with the column that displays the HR (Home Result) in the Premier League tab on your spreadsheet”

How to enter the code

Select Cell C4 in the Stats UK – POI tab,

How to build a Football Betting Model - Home Games Won

How to build a Football Betting Model – Home Games Won

Type

=SUMPRODUCT((

Now, as before, click the Premier League tab, and find the Home Team Name Column, and select row 15 of this column, drag down to row 20

then press =

Click back on the Stats UK – POI tab and select cell A4

Now press )*((

click the Premier League tab, and find the Home Result Column, and select row 15 of this column, drag down to row 20

then press =

Click back on the Stats UK – POI tab and select cell C3 (W)

Now press )))

and Enter

=SUMPRODUCT((‘Premier League’!FK15:FK20=A4)*((‘Premier League’!FF15:FF20=’Stats UK – POI’!$C$3)))

In between FK & FF  and 15 add a $   Same after the 2nd FK & FF

Delete 20 and enter 1044

Your final formula will look like this

=SUMPRODUCT((‘Premier League’!FK$15:FK$1044=A4)*((‘Premier League’!$FF$15:$FF$1044=’Stats UK – POI’!$C$3)))

Copy and Paste for the rest of the column

Win %

Click in Cell D4

Enter

=C4/B4

Press Enter

Copy and Paste for the rest of the column

Do not worry if it shows an error #DIV/0! This just means that no home games have been played yet for that particular team.

Repeat for Draws & Losses

The formula’s you have just entered need to be repeated for Draws and Losses

How to build a Football Betting Model - Home Games Won

How to build a Football Betting Model – Home Games Won

The Key Part to change is highlighted in Red in the above image, in the calculation below, change the C to a E for Draws and to G for Losses.

=SUMPRODUCT((‘Premier League’!FK$15:FK$1044=A4)*((‘Premier League’!$FF$15:$FF$1044=’Stats UK – POI’!$C$3)))

For the Draw and Loss % change also change the C for E & G.

We will look at the Goals Scored, Conceded, PTS and GD in our next post.

 

Make sure you follow our page on Facebook – How to build a Football Betting Model for all the updates

Upgrade your Betting Knowledge..

  • Upgrade your Betting Knowledge..

    Getting Betting Guide & Great tips sent straight to your inbox!

  • Winning with our bets ? Let us know or subscribe to our RSS feed Sign up to BET365 now for the best deals around
    Tagged , , , . Bookmark the permalink.