Most recent 6 values

RedPed

New Member
Joined
Jul 10, 2015
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
I have some football data where I have data for completed matches and also upcoming fixtures.

I would like to show the the number of goals scored by each team form their most recent 6 matches played (note that this will include both home and away matches played by the teams). example screenshot of completed matches below and an example of the upcoming fixtures. Is this possible or do I need to think of another solution?

1691431473247.png
1691431660001.png
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi I see nobody had a go at this one, presumably as your profile says Excel 2016. So I took the challenge to see if I could make it work with O365, in the event you might upgrade at some point.
It pretty much does what you want, except I get a #value error when a team has played only home or away game (ie. team name exists in only 1 column. Over a season that would correct itself, but might show up say for example a new team just promoted, has played only his first game. It would self fix for the next match.
I tried to get an error capture for it, but failed .. no doubt someone here could help more I imagine.

Anyway, 2 formulas, one for home team goals, one for away team goals. The yellow cells were just for me to easily see Brentfords goals whilst building the formula.

cheers
Rob
football scores.xlsx
ABCDEFGHIJKLMNOPQRS
1DateHome TeamAway TeamHome Team GoalsAway Team GoalsHT Goals 1HT Goals 2HT Goals 3HT Goals 4HT Goals 5HT Goals 6AT Goals 1AT Goals 2AT Goals 3AT Goals 4AT Goals 5AT Goals 6
213/08/2021BrentfordArsenal2000512000
314/08/2021NorwichLiverpool030023
414/08/2021EvertonSouthampton312311
514/08/2021ChelseaBrentford3023005120
614/08/2021Man UtdLeeds511521
714/08/2021WatfordAston Villa320322
815/08/2021BurnleyBrentford1201005120
914/08/2021LeicesterWolves10#VALUE!#VALUE!
1016/08/2021BrentfordMan City10005120#VALUE!
1115/08/2021NewcastleWest Ham2402#VALUE!
1221/08/2021LiverpoolBurnley202301
1321/08/2021Aston VillaNewcastle202202
1421/08/2021BrentfordNorwich5000512000
1523/08/2021C PalaceBrentford00#VALUE!005120
1621/08/2021LeedsEverton222123
1721/08/2021BrightonWatford20#VALUE!03
1822/08/2021SouthamptonMan Utd111115
1922/08/2021ArsenalChelsea020023
2024/08/2021BrentfordTottenham01005120#VALUE!
Sheet1
Cell Formulas
RangeFormula
H2:M2,H20:M20,H18:I19,H16:I16,H14:M14,H11:I13,H10:M10,H9,H15,H17,H3:I8H2=TRANSPOSE(LET(scores,CHOOSECOLS(SORT(VSTACK(CHOOSECOLS(FILTER($A$2:$E$20,$B$2:$B$20=$B2,"no team"),1,2,4),CHOOSECOLS(FILTER($A$2:$E$20,$C$2:$C$20=$B2,"no team"),1,3,5)),1,-1),3),IF(ROWS(scores)>6,CHOOSEROWS(scores,1,2,3,4,5,6),scores)))
N16:O19,N15:S15,N12:O14,N9:N11,N20,N8:S8,N6:O7,N5:S5,N2:O4N2=TRANSPOSE(LET(scores,CHOOSECOLS(SORT(VSTACK(CHOOSECOLS(FILTER($A$2:$E$20,$B$2:$B$20=$C2,"no team"),1,2,4),CHOOSECOLS(FILTER($A$2:$E$20,$C$2:$C$20=$C2,"no team"),1,3,5)),1,-1),3),IF(ROWS(scores)>6,CHOOSEROWS(scores,1,2,3,4,5,6),scores)))
Dynamic array formulas.
 
Upvote 0
@RobP The OP's profile clearly shows 2016 & so does not have most of those functions as they only exist in 365
 
Upvote 0
@RobP The OP's profile clearly shows 2016 & so does not have most of those functions as they only exist in 365
To be fair, @RobP did at least acknowledge that
as your profile says Excel 2016. So I took the challenge to see if I could make it work with O365, in the event you might upgrade at some point.


I see nobody had a go at this one, presumably as your profile says Excel 2016.
I had not seen this one so I'll have a go at it now for 2016. I have assumed that in the bottom right image in post 1 that 'HT Goals 1' means the goals scored by the Home team in that bottom right image in the latest game played. That is Liverpool goals in their last match. 'HT Goals 2' would be Liverpool's goals in their second last match etc.
Manual colour coding was also just for my benefit in checking.

23 08 22.xlsm
ABCDEFGHIJKLMNOPQRSTU
1DateHome TeamAway TeamHome Team GoalsAway Team GoalsHT Goals 1HT Goals 2HT Goals 3HT Goals 4HT Goals 5HT Goals 6AT Goals 1AT Goals 2AT Goals 3AT Goals 4AT Goals 5AT Goals 6
213/08/2021BrentfordArsenal20LiverpoolArsenal23    00    
314/08/2021NorwichLiverpool03LeedsWolves21    0     
414/08/2021EvertonSouthampton31
514/08/2021ChelseaBrentford30
614/08/2021Man UtdLeeds51
714/08/2021WatfordAston Villa32
815/08/2021BurnleyBrentford12
914/08/2021LeicesterWolves10
1016/08/2021BrentfordMan City10
1115/08/2021NewcastleWest Ham24
1221/08/2021LiverpoolBurnley20
1321/08/2021Aston VillaNewcastle20
1421/08/2021BrentfordNorwich50
1523/08/2021C PalaceBrentford00
1621/08/2021LeedsEverton22
1721/08/2021BrightonWatford20
1822/08/2021SouthamptonMan Utd11
1922/08/2021ArsenalChelsea02
2024/08/2021BrentfordTottenham01
Goals in last 6
Cell Formulas
RangeFormula
J2:O3J2=IFERROR(INDEX($D:$E,AGGREGATE(14,6,ROW($D$2:$E$20)/(($B$2:$B$20=$H2)+($C$2:$C$20=$H2)),COLUMNS($J:J)),1+(INDEX($C:$C,AGGREGATE(14,6,ROW($B$2:$B$20)/(($B$2:$B$20=$H2)+($C$2:$C$20=$H2)),COLUMNS($J:J)))=$H2)),"")
P2:U3P2=IFERROR(INDEX($D:$E,AGGREGATE(14,6,ROW($D$2:$E$20)/(($B$2:$B$20=$I2)+($C$2:$C$20=$I2)),COLUMNS($P:P)),1+(INDEX($C:$C,AGGREGATE(14,6,ROW($B$2:$B$20)/(($B$2:$B$20=$I2)+($C$2:$C$20=$I2)),COLUMNS($P:P)))=$I2)),"")
 
Upvote 0
No worries @Fluff & thanks @Peter_SSs. at least he has his answer now - but I don't have mine :-( Any idea how to trap that #value issue I was getting because of the "filter" command .. ?

Just as I couldn't find a way to wrap it myself.

cheers
Rob
 
Upvote 0
but I don't have mine :( Any idea how to trap that #value issue I was getting
Just wrap your whole formula in an IFERROR?
Excel Formula:
=IFERROR(your_formula,"")
 
Upvote 0
Hi Pete,

if only it were that simple ... If I wrap it all up, and say just insert the home team goal (as theres only going to be one) instead - it works. BUT, when the home team has played 2 home games only it inserting wrong data. So not quite there.

I was trying to wrap it up around the filter commands inside, but was getting true/false array data back instead of my results .. so failed miserably.

Anyway, glad its not just me ...

cheers
Rob
 
Upvote 0
if only it were that simple ...
To be honest, I hadn't studied your formula in detail so suggested what seemed likely, but obviously wasn't. 😎

What about this then?

23 08 22.xlsm
ABCDEFGHIJKLMNOPQRS
1DateHome TeamAway TeamHome Team GoalsAway Team GoalsHT Goals 1HT Goals 2HT Goals 3HT Goals 4HT Goals 5HT Goals 6AT Goals 1AT Goals 2AT Goals 3AT Goals 4AT Goals 5AT Goals 6
218/08/2021BrentfordArsenal20020
319/08/2021AZ5725123456
420/08/2021C PalaceZ060123456
521/08/2021AEverton22252
622/08/2021BZ151123456
723/08/2021CZ242123456
824/08/2021DZ333123456
925/08/2021EZ424123456
1026/08/2021BrentfordZ0102123456
Goals in last 6 (2)
Cell Formulas
RangeFormula
H10:I10,H5:I5,H4,H6:H9,H2:I3H2=TRANSPOSE(LET(scores,CHOOSECOLS(SORT(VSTACK(CHOOSECOLS(FILTER($A$2:$E$10,$B$2:$B$10=$B2,{"","","",""}),1,2,4),CHOOSECOLS(FILTER($A$2:$E$10,$C$2:$C$10=$B2,{"","","","",""}),1,3,5)),1,-1),3),scores2,FILTER(scores,scores<>""),IF(ROWS(scores2)>6,CHOOSEROWS(scores2,1,2,3,4,5,6),scores2)))
N2,N5,N6:S10,N3:S4N2=TRANSPOSE(LET(scores,CHOOSECOLS(SORT(VSTACK(CHOOSECOLS(FILTER($A$2:$E$10,$B$2:$B$10=$C2,{"","","",""}),1,2,4),CHOOSECOLS(FILTER($A$2:$E$10,$C$2:$C$10=$C2,{"","","","",""}),1,3,5)),1,-1),3),scores2,FILTER(scores,scores<>""),IF(ROWS(scores2)>6,CHOOSEROWS(scores2,1,2,3,4,5,6),scores2)))
Dynamic array formulas.


Here is another 365 alternative to consider though:

23 08 22.xlsm
ABCDEFGHIJKLMNOPQRS
1DateHome TeamAway TeamHome Team GoalsAway Team GoalsHT Goals 1HT Goals 2HT Goals 3HT Goals 4HT Goals 5HT Goals 6AT Goals 1AT Goals 2AT Goals 3AT Goals 4AT Goals 5AT Goals 6
218/08/2021BrentfordArsenal20020
319/08/2021AZ5725123456
420/08/2021C PalaceZ060123456
521/08/2021AEverton22252
622/08/2021BZ151123456
723/08/2021CZ242123456
824/08/2021DZ333123456
925/08/2021EZ424123456
1026/08/2021BrentfordZ0102123456
Goals in last 6 (3)
Cell Formulas
RangeFormula
H10:I10,H5:I5,H4,H6:H9,H2:I3H2=TAKE(TOROW(IF(SORTBY(B$2:C$10,A$2:A$10,-1)=B2,SORTBY(D$2:E$10,A$2:A$10,-1),1/0),2),,6)
N2,N5,N6:S10,N3:S4N2=TAKE(TOROW(IF(SORTBY(B$2:C$10,A$2:A$10,-1)=C2,SORTBY(D$2:E$10,A$2:A$10,-1),1/0),2),,6)
Dynamic array formulas.
 
Upvote 0
Thanks @Peter_SSs - its ok, you're not the first on here to not read all the details before writing an answer ;). (Myself included).

Works great as I had planned (even if not quite what the OP had in mind - your Xl2016 fixed all that though). I can't find any documentation to explain what those curly braces and "" are doing in the FILTER - but will keep looking. I prefer your last suggestion though, as a) looks much cleaner, and b) its a new take and new stuff for me to learn instead of my goto FILTER .... I'm off to figure it out now..

thanks a lot for the learning as usual.
Rob

sorry, I take it back :) the 2nd example you gave doesn't like the Away Team goals for some reason where 1 instance of the team exists.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top