Thanks also to Markmzz for taking the time to help - thankyou.
Div | Date | HomeTeam | AwayTeam | Is last 5 home? | Is last 5 away? |
FLC | 11/09/2020 | Watford | Middlesbrough | ||
EPL | 12/09/2020 | Fulham | Arsenal | ||
FLC | 12/09/2020 | Birmingham | Brentford | ||
FLC | 12/09/2020 | Wycombe | Rotherham | ||
EPL | 12/09/2020 | Crystal Palace | Southampton | ||
FLC | 12/09/2020 | Barnsley | Luton | ||
FLC | 12/09/2020 | Bournemouth | Blackburn | ||
FLC | 12/09/2020 | Bristol City | Coventry | ||
FLC | 12/09/2020 | Cardiff | Sheffield Weds | ||
FLC | 12/09/2020 | Derby | Reading | ||
FLC | 12/09/2020 | Huddersfield | Norwich | ||
FLC | 12/09/2020 | Millwall | Stoke | ||
FLC | 12/09/2020 | Preston | Swansea | ||
FLC | 12/09/2020 | QPR | Nott'm Forest | ||
FL1 | 12/09/2020 | Accrington | Peterboro | ||
FL1 | 12/09/2020 | Crewe | Charlton | ||
FL1 | 12/09/2020 | Doncaster | Milton Keynes Dons | ||
FL1 | 12/09/2020 | Fleetwood Town | Burton | ||
FL1 | 12/09/2020 | Gillingham | Hull | ||
FL1 | 12/09/2020 | Lincoln | Oxford | ||
FL1 | 12/09/2020 | Northampton | AFC Wimbledon | ||
FL1 | 12/09/2020 | Plymouth | Blackpool | ||
FL1 | 12/09/2020 | Portsmouth | Shrewsbury | ||
FL1 | 12/09/2020 | Sunderland | Bristol Rvs | ||
FL1 | 12/09/2020 | Swindon | Rochdale | ||
FL2 | 12/09/2020 | Barrow | Stevenage | ||
FL2 | 12/09/2020 | Bolton | Forest Green | ||
FL2 | 12/09/2020 | Bradford | Colchester | ||
FL2 | 12/09/2020 | Cambridge | Carlisle | ||
FL2 | 12/09/2020 | Cheltenham | Morecambe | ||
FL2 | 12/09/2020 | Mansfield | Tranmere | ||
FL2 | 12/09/2020 | Oldham | Leyton Orient | ||
FL2 | 12/09/2020 | Port Vale | Crawley Town | ||
FL2 | 12/09/2020 | Salford | Exeter | ||
FL2 | 12/09/2020 | Scunthorpe | Newport County | ||
FL2 | 12/09/2020 | Southend | Harrogate | ||
FL2 | 12/09/2020 | Walsall | Grimsby | ||
EPL | 12/09/2020 | Liverpool | Leeds | ||
EPL | 12/09/2020 | West Ham | Newcastle | ||
FL1 | 13/09/2020 | Ipswich | Wigan | ||
EPL | 13/09/2020 | West Brom | Leicester | ||
EPL | 13/09/2020 | Tottenham | Everton | ||
EPL | 14/09/2020 | Sheffield United | Wolves | ||
EPL | 14/09/2020 | Brighton | Chelsea | ||
FLC | 18/09/2020 | Coventry | QPR | ||
EPL | 19/09/2020 | Everton | West Brom | ||
FLC | 19/09/2020 | Nott'm Forest | Cardiff | ||
FL2 | 19/09/2020 | Carlisle | Southend | ||
EPL | 19/09/2020 | Leeds | Fulham | ||
FLC | 19/09/2020 | Blackburn | Wycombe | ||
FLC | 19/09/2020 | Brentford | Huddersfield | ||
FLC | 19/09/2020 | Luton | Derby | ||
FLC | 19/09/2020 | Middlesbrough | Bournemouth | ||
FLC | 19/09/2020 | Norwich | Preston | ||
FLC | 19/09/2020 | Reading | Barnsley | ||
FLC | 19/09/2020 | Rotherham | Millwall | ||
FLC | 19/09/2020 | Sheffield Weds | Watford | ||
FLC | 19/09/2020 | Swansea | Birmingham | ||
FL1 | 19/09/2020 | AFC Wimbledon | Plymouth | ||
FL1 | 19/09/2020 | Blackpool | Swindon | ||
FL1 | 19/09/2020 | Bristol Rvs | Ipswich | ||
FL1 | 19/09/2020 | Burton | Accrington | ||
FL1 | 19/09/2020 | Charlton | Doncaster | ||
FL1 | 19/09/2020 | Hull | Crewe | ||
FL1 | 19/09/2020 | Milton Keynes Dons | Lincoln | ||
FL1 | 19/09/2020 | Oxford | Sunderland | ||
FL1 | 19/09/2020 | Peterboro | Fleetwood Town | ||
FL1 | 19/09/2020 | Shrewsbury | Northampton | ||
FL1 | 19/09/2020 | Wigan | Gillingham | ||
FL2 | 19/09/2020 | Colchester | Bolton | ||
FL2 | 19/09/2020 | Crawley Town | Scunthorpe | ||
FL2 | 19/09/2020 | Exeter | Port Vale | ||
FL2 | 19/09/2020 | Forest Green | Bradford | ||
FL2 | 19/09/2020 | Grimsby | Salford | ||
FL2 | 19/09/2020 | Harrogate | Walsall | ||
FL2 | 19/09/2020 | Leyton Orient | Mansfield | ||
FL2 | 19/09/2020 | Morecambe | Cambridge | ||
FL2 | 19/09/2020 | Newport County | Barrow | ||
FL2 | 19/09/2020 | Stevenage | Oldham | ||
FL2 | 19/09/2020 | Tranmere | Cheltenham | ||
EPL | 19/09/2020 | Man United | Crystal Palace | ||
EPL | 19/09/2020 | Arsenal | West Ham | ||
EPL | 20/09/2020 | Southampton | Tottenham | ||
EPL | 20/09/2020 | Newcastle | Brighton | ||
FLC | 20/09/2020 | Stoke | Bristol City | ||
FL1 | 20/09/2020 | Rochdale | Portsmouth | ||
EPL | 20/09/2020 | Chelsea | Liverpool | ||
EPL | 20/09/2020 | Leicester | Burnley | ||
EPL | 21/09/2020 | Aston Villa | Sheffield United | ||
EPL | 21/09/2020 | Wolves | Man City | ||
FLC | 25/09/2020 | Huddersfield | Nott'm Forest | ||
EPL | 26/09/2020 | Brighton | Man United | ||
FLC | 26/09/2020 | Watford | Luton | ||
FLC | 26/09/2020 | Wycombe | Swansea | ||
EPL | 26/09/2020 | Crystal Palace | Everton | ||
FLC | 26/09/2020 | Barnsley | Coventry | ||
FLC | 26/09/2020 | Birmingham | Rotherham | ||
FLC | 26/09/2020 | Cardiff | Reading | ||
FLC | 26/09/2020 | Derby | Blackburn |
=COUNTIF(C2:C$1500,C2)<=5
Book2.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Date | HomeTeam | AwayTeam | FTHG | FTAG | 2 | Total goals last n games from formula | Total goals last 2 games actual | |||||||
2 | 31/01/2018 | Chelsea | Bournemouth | 0 | 3 | Watford | 1 | 3 | 0 | 3 | |||||
3 | 31/01/2018 | Everton | Leicester | 2 | 1 | 2 | 1 | 2 | 2 | 1 | |||||
4 | 31/01/2018 | Man City | West Brom | 3 | 0 | 4 | 1 | 3 | 0 | ||||||
5 | 31/01/2018 | Newcastle | Burnley | 1 | 1 | 2 | 3 | 1 | 1 | ||||||
6 | 31/01/2018 | Southampton | Brighton | 1 | 1 | 1 | 6 | 1 | 1 | ||||||
7 | 03/02/2018 | Arsenal | Everton | 5 | 1 | 1 | 1 | 5 | 1 | ||||||
8 | 03/02/2018 | Bournemouth | Stoke | 2 | 1 | 3 | 1 | 2 | 1 | ||||||
9 | 03/02/2018 | Brighton | West Ham | 3 | 1 | 6 | 2 | 3 | 1 | ||||||
10 | 03/02/2018 | Burnley | Man City | 1 | 1 | 3 | 4 | 2 | 4 | ||||||
11 | 03/02/2018 | Leicester | Swansea | 1 | 1 | 2 | 5 | 2 | 1 | ||||||
12 | 03/02/2018 | Man United | Huddersfield | 2 | 0 | 5 | 2 | 2 | 0 | ||||||
13 | 03/02/2018 | West Brom | Southampton | 2 | 3 | 1 | 1 | 2 | 4 | ||||||
14 | 04/02/2018 | Crystal Palace | Newcastle | 1 | 1 | 2 | 2 | 2 | 0 | ||||||
15 | 04/02/2018 | Liverpool | Tottenham | 2 | 2 | 6 | 3 | 2 | 2 | ||||||
16 | 05/02/2018 | Watford | Chelsea | 4 | 1 | 2 | 1 | 4 | 1 | ||||||
17 | 10/02/2018 | Everton | Crystal Palace | 3 | 1 | 1 | 2 | 4 | 2 | ||||||
18 | 10/02/2018 | Man City | Leicester | 5 | 1 | 4 | 2 | 6 | 2 | ||||||
19 | 10/02/2018 | Stoke | Brighton | 1 | 1 | 1 | 6 | 2 | 4 | ||||||
20 | 10/02/2018 | Swansea | Burnley | 1 | 0 | 5 | 3 | 2 | 1 | ||||||
21 | 10/02/2018 | Tottenham | Arsenal | 1 | 0 | 3 | 1 | 3 | 5 | ||||||
22 | 10/02/2018 | West Ham | Watford | 2 | 0 | 2 | 2 | 3 | 4 | ||||||
23 | 11/02/2018 | Huddersfield | Bournemouth | 4 | 1 | 2 | 3 | 4 | 3 | ||||||
24 | 11/02/2018 | Newcastle | Man United | 1 | 0 | 2 | 5 | 2 | 2 | ||||||
25 | 11/02/2018 | Southampton | Liverpool | 0 | 2 | 1 | 6 | 3 | 4 | ||||||
26 | 12/02/2018 | Chelsea | West Brom | 3 | 0 | 1 | 1 | 4 | 2 | ||||||
27 | 24/02/2018 | Bournemouth | Newcastle | 2 | 2 | 3 | 2 | 3 | 3 | ||||||
28 | 24/02/2018 | Brighton | Swansea | 4 | 1 | 6 | 5 | 5 | 2 | ||||||
29 | 24/02/2018 | Burnley | Southampton | 1 | 1 | 3 | 1 | 1 | 1 | ||||||
30 | 24/02/2018 | Leicester | Stoke | 1 | 1 | 2 | 1 | 2 | 2 | ||||||
31 | 24/02/2018 | Liverpool | West Ham | 4 | 1 | 6 | 2 | 6 | 3 | ||||||
32 | 24/02/2018 | Watford | Everton | 1 | 0 | 2 | 1 | 1 | 3 | ||||||
33 | 24/02/2018 | West Brom | Huddersfield | 1 | 2 | 1 | 2 | 1 | 6 | ||||||
34 | 25/02/2018 | Crystal Palace | Tottenham | 0 | 1 | 2 | 3 | 1 | 2 | ||||||
35 | 25/02/2018 | Man United | Chelsea | 2 | 1 | 5 | 1 | 2 | 4 | ||||||
36 | 01/03/2018 | Arsenal | Man City | 0 | 3 | 1 | 4 | 0 | 8 | ||||||
37 | 03/03/2018 | Burnley | Everton | 2 | 1 | 3 | 1 | 3 | 1 | ||||||
38 | 03/03/2018 | Leicester | Bournemouth | 1 | 1 | 2 | 3 | 2 | 3 | ||||||
39 | 03/03/2018 | Liverpool | Newcastle | 2 | 0 | 6 | 2 | 6 | 2 | ||||||
40 | 03/03/2018 | Southampton | Stoke | 0 | 0 | 1 | 1 | 1 | 1 | ||||||
41 | 03/03/2018 | Swansea | West Ham | 4 | 1 | 5 | 2 | 5 | 2 | ||||||
42 | 03/03/2018 | Tottenham | Huddersfield | 2 | 0 | 3 | 2 | 3 | 2 | ||||||
43 | 03/03/2018 | Watford | West Brom | 1 | 0 | 2 | 1 | 2 | 1 | ||||||
44 | 04/03/2018 | Brighton | Arsenal | 2 | 1 | 6 | 1 | 6 | 1 | ||||||
45 | 04/03/2018 | Man City | Chelsea | 1 | 0 | 4 | 1 | 4 | 1 | ||||||
46 | 05/03/2018 | Crystal Palace | Man United | 2 | 3 | 2 | 5 | 2 | 5 | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2:I4 | I2 | =SUM(IF(ROW($A$2:$A$59)>=LARGE(IF($B$2:$C$59=B$2,ROW($A$2:$A$59)),MIN($G$1,COUNTIFS($B$2:$C$59,B2))),IF($B$2:$C$59=B2,$D$2:$E$59))) |
I5:J46,J2:J4 | J2 | =SUM(IF(ROW($A$2:$A$59)>=LARGE(IF($B$2:$C$59=C2,ROW($A$2:$A$59)),MIN($G$1,COUNTIFS($B$2:$C$59,C2))),IF($B$2:$C$59=C2,$D$2:$E$59))) |
G3 | G3 | =SUM(IF(ROW($A$2:$A$59)>=LARGE(IF($B$2:$C$59=G$2,ROW($A$2:$A$59)),MIN($G$1,COUNTIFS($B$2:$C$59,G$2))),IF($B$2:$C$59=G$2,$D$2:$E$59))) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
As this is a significantly different question from the op, please start a new thread of your own.
Also if you are trying to do it season by season it would help if you supplied more than one years data.