premier league football predictor - unable to calculate draws / include form

**Gimmers** · 08-27-2019, 07:57 AM

I’ve been looking to put together a premier league score predictor so that myself and some colleagues at work can predict the score on a weekly basis.

Everything (seems) to work however I was wanting to put a premier league table as an extra sheet (which I can then go and use to formulate tables based on our predictions similar to what happens on the BBC sport website)

Everything is working except for the ‘Draw’ part Columns ‘Q’ and ‘Y’ on the ‘Home and Away’ Sheet

I did have the formula

=SUMPRODUCT((Fixtures!$B$2:$B$600=$B5)*(Fixtures!$H$2:$H$600=Fixtures!$J$2:$J$600)*(Fixtures!$H$2:$H$600<>””))

However this just returns an error. Changing the “” to ‘)’ fixes it but I still get an issue where if the draw is 0-0 then it is not registered.

I have uploaded the spreadsheet, also if anyone would like to use this then feel free.

But would someone be able to take a look and advise where I have gone wrong / how the formula should read.

As an addition could anyone advise how I could add a ‘form’ section that shows what each team has done in there last 5 matches? This would be added from column K on the 'PL Table' Sheet

Many thanks

**AliGW** · 08-27-2019, 08:02 AM

Welcome to the forum.

Try this:

=SUMPRODUCT((Fixtures!$B$2:$B$600=$B5)*(Fixtures!$H$2:$H$600=Fixtures!$J$2:$J$600)*(Fixtures!$H$2:$H$600<>""))

Notice you need STRAIGHT QUOTES. *

As an addition could anyone advise how I could add a ‘form’ section that shows what each team has done in there last 5 matches?

Please mock up what you mean for a few teams.

* Note that if you are working on an iPad, then you need to press the " key and select the straight quote option from the popup - just pressing the key will insert slanter quotes.

**AliGW** · 08-27-2019, 08:24 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Gimmers** · 08-27-2019, 09:06 AM

Hi, thank you so much that worked great

What i mean by form is, if you look at the PL Table sheet in cells K-O for example is there anyway of having a W, L or a D for win lose of draw depending on what happened in the teams last five matches.

**AliGW** · 08-27-2019, 09:12 AM

What does this mean?

Excel 2016 (Windows) 32 bit

	K	L	M
4	Last 5 Games
5	W	W	W
6	W	D	W
7	W	W	L

Sheet: PL Table

Are the games in ascending or descending order from left to right?

**AliGW** · 08-27-2019, 09:16 AM

And can we add helper columns (that could be hidden) to the fixtures sheet to facilitate this?

**Gimmers** · 08-27-2019, 09:53 AM

Hi, That wouldnt be an issue to add those columns in.

The way the PL table sheet is set out is that K4:O4 are merged to become K4 just with a title of 'last 5 games' Then the Cells K4,L4,M4,N4,O4 have W/D/L in them with the furthest right column being the oldest result I.E after five games K4 is updated with the newest result pushing the rest to the right, If that makes sense.

**CAntosh** · 08-27-2019, 01:07 PM

There might be an easier way, but where's the fun in that? Try the following formula in K5. It must be array-entered (confirm with Ctrl + Shift + Enter instead of the regular Enter), then filled right through O5 and down through the bottom of the table.

=IFERROR(IF($B5=INDEX(Fixtures!$B$6:$B$541,LARGE(IF(((Fixtures!$B$6:$B$541=$B5)+(Fixtures!$F$6:$F$541=$B5))*(Fixtures!$H$6:$H$541 < > "")=1,ROW(Fixtures!$B$6:$B$541)-ROW(Fixtures!$B$6)+1),COLUMN(A:A))),IF(INDEX(Fixtures!$H$6:$H$541,LARGE(IF(((Fixtures!$B$6:$B$541=$B5)+(Fixtures!$F$6:$F$541=$B5))*(Fixtures!$H$6:$H$541 < > "")=1,ROW(Fixtures!$B$6:$B$541)-ROW(Fixtures!$B$6)+1),COLUMN(A:A))) > INDEX(Fixtures!$J$6:$J$541,LARGE(IF(((Fixtures!$B$6:$B$541=$B5)+(Fixtures!$F$6:$F$541=$B5))*(Fixtures!$H$6:$H$541 < > "")=1,ROW(Fixtures!$B$6:$B$541)-ROW(Fixtures!$B$6)+1),COLUMN(A:A))),"W",IF(INDEX(Fixtures!$H$6:$H$541,LARGE(IF(((Fixtures!$B$6:$B$541=$B5)+(Fixtures!$F$6:$F$541=$B5))*(Fixtures!$H$6:$H$541 < > "")=1,ROW(Fixtures!$B$6:$B$541)-ROW(Fixtures!$B$6)+1),COLUMN(A:A))) < INDEX(Fixtures!$J$6:$J$541,LARGE(IF(((Fixtures!$B$6:$B$541=$B5)+(Fixtures!$F$6:$F$541=$B5))*(Fixtures!$H$6:$H$541 < >"")=1,ROW(Fixtures!$B$6:$B$541)-ROW(Fixtures!$B$6)+1),COLUMN(A:A))),"L","D")),IF(INDEX(Fixtures!$H$6:$H$541,LARGE(IF(((Fixtures!$B$6:$B$541=$B5)+(Fixtures!$F$6:$F$541=$B5))*(Fixtures!$H$6:$H$541 < >"")=1,ROW(Fixtures!$B$6:$B$541)-ROW(Fixtures!$B$6)+1),COLUMN(A:A))) > INDEX(Fixtures!$J$6:$J$541,LARGE(IF(((Fixtures!$B$6:$B$541=$B5)+(Fixtures!$F$6:$F$541=$B5))*(Fixtures!$H$6:$H$541 < > "")=1,ROW(Fixtures!$B$6:$B$541)-ROW(Fixtures!$B$6)+1),COLUMN(A:A))),"L",IF(INDEX(Fixtures!$H$6:$H$541,LARGE(IF(((Fixtures!$B$6:$B$541=$B5)+(Fixtures!$F$6:$F$541=$B5))*(Fixtures!$H$6:$H$541 < > "")=1,ROW(Fixtures!$B$6:$B$541)-ROW(Fixtures!$B$6)+1),COLUMN(A:A))) < INDEX(Fixtures!$J$6:$J$541,LARGE(IF(((Fixtures!$B$6:$B$541=$B5)+(Fixtures!$F$6:$F$541=$B5))*(Fixtures!$H$6:$H$541 < > "")=1,ROW(Fixtures!$B$6:$B$541)-ROW(Fixtures!$B$6)+1),COLUMN(A:A))),"W","D"))),"")

**CAntosh** · 08-27-2019, 01:08 PM

I had to put some spaces in the formula above to dodge our firewall, so maybe just pull the formula from the attachment:

**Gimmers** · 08-28-2019, 03:32 AM

Oh my word, that works, thank you so much for your help with this it really is appreciated. and hopefully it helps someone else looking for the same answer.

**CAntosh** · 08-28-2019, 09:50 AM

Glad to help, good luck!

**Bob Phillips** · 08-28-2019, 10:17 AM

You should also change your ranking formula on Home & Away to cater for ties and not get the #N/A that you are getting on PL Table, something like

=RANK(G5,$G$5:$G$24,1)+COUNTIF($G$5:$G5,G5)-1

premier league football predictor - unable to calculate draws / include form

LinkBack

Thread Tools

Rate This Thread

Display

premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Re: premier league football predictor - unable to calculate draws / include form

Thread Information

Users Browsing this Thread

Similar Threads

DFS Football Predictor Model

Football Predictor

[SOLVED] Formula required to calculate points based on football scores in champions league

does anyone know anything about premier league football here?

Favourite English Premier League Teams

Football League Predictor Workbook

Premier League Stats

Bookmarks

Bookmarks

Posting Permissions