Looking for a better and worse sequence

[Marvo]

Hi All, I have a large workbook that involves all the matches of Northampton Town FC. On the worksheet I have a cell (W1) that you can put however many matches you like and you can see how many points have been gained from those most recent matches (current form). This works fine.
In W5 I have for League matches only
=SUMPRODUCT(COUNTIFS(INDEX(ALL!K:K,LARGE(INDEX(ROW(ALL!$K:$K)*(ALL!$K:$K<>"")*(ALL!$J:$J=$V$5),),$W$1)):ALL!$K$9999,{"W","D"},INDEX(ALL!J:J,LARGE(INDEX(ROW(ALL!$K:$K)*(ALL!$K:$K<>"")*(ALL!$J:$J=$V$5),),$W$1)):ALL!$J$9999,$V$5),{3,1})
In W7 I have for all matches
=SUMPRODUCT(COUNTIFS(INDEX(ALL!K:K,LARGE(INDEX(ROW(ALL!$K:$K)*(ALL!$K:$K<>""),),$W$1)):ALL!$K$9999,{"W","D"},INDEX(ALL!J:J,LARGE(INDEX(ROW(ALL!$K:$K)*(ALL!$K:$K<>""),),$W$1)):ALL!$J$9999,$V$5),{3,1})

Now somebody several years ago placed VBA on the worksheet that would look back to find the last time the most recent sequence was either better or worse (and other things). This was very useful but sadly it went awry and I don't don't understand VBA and now its disappeared altogether. So what I'm wondering is whether I could put a formula in a cell to do the same job?

So just to reiterate, if the club in its last 5 games had just won its last 4 league games and drawn another = 13pts, the formula would look for the previous sequence of 5 games when that was bettered (that could only be 5 wins).
Alternatively if the club was in a slump and in its last 5 games it had lost 4 and drawn 1 = 1pt, it would look for the last time the club did worse than that which would have to be 5 sequential defeats = 0pts.

Now all this makes sense to me when I read it back but I'm guessing it wont make any sense to anyone else. My question is, it it possible to do this with a formula?

 

[StephenCrump]

I'd put a couple of helper columns in your "ALL" worksheet, along these lines:

	A	B	C	D	E	F	G
1	Seq Length (N)	5
2						Points	Points
3			Goals	Goals		League	All
4	Date	Fixture	For	Against	Points	Sequence	Sequence
5	17 Feb 2024	FA Cup	1	2	0	-	-
6	24 Feb 2024	League	1	0	3	-	-
7	2 Mar 2024	FA Cup	1	2	0	-	-
8	9 Mar 2024	League	3	0	3	-	-
9	16 Mar 2024	League	0	0	1	-	7
10	23 Mar 2024	League	1	3	0	-	7
11	30 Mar 2024	League	0	2	0	7	4
12	6 Apr 2024	FA Cup	0	0	1	-	5
13	13 Apr 2024	League	1	4	0	4	2
14	20 Apr 2024	League	2	2	1	2	2
15	27 Apr 2024	League	0	1	0	1	2
16	4 May 2024	Charity	3	2	3	-	5
17	11 May 2024	League	1	1	1	2	5
18	18 May 2024	League	3	0	3	5	8
Sheet1

Cell Formulas
Range	Formula
E5:E18	E5	=(C5=D5)+3*(C5>D5)
F5:F18	F5	=LET(p,TAKE(FILTER(E$5:E5,B$5:B5="League",1),-N),IF(AND(B5="League",ROWS(p)>=N),SUM(p),"-"))
G5:G18	G5	=IF(ROWS(C$5:C5)>=N,SUM(E1:E5),"-")

Named Ranges
Name	Refers To	Cells
N	=Sheet1!$B$1	F5:G18

Then it's relatively simple to write additional formulae to identify the date of the previous better/worse points sequence.

But please let us know first whether these formulae work for you, i.e. whether you have access to the LET, TAKE and FILTER functions?

If not, the formulae can also be rewritten for older versions of Excel.

 

[Marvo]

Many thanks for the reply, I'll look at it in the morning, see how it goes.

I have the latest excel (I think) but not 365.

 

[Marvo]

I'll just add I used to have several helper columns but with over 5,000 rows it really slowed my workbook down.

 

[Marvo]

Hi. Sorry for the delay, finally got time to get back to this. This is as far as I've got.

I already had a column (K) with the result in, either W, D or L so it was relatively easy using SWITCH to construct the two helper columns with points from the games.

ALL GAMES (Column AF)
=SWITCH(K2,"W",3,"D",1,"L","")

LEAGUE GAMES (Column AG)
=IF(J2="League",SWITCH(K2,"W",3,"D",1,""),"")

I don't know where to go from this point in regard the best and worst sequences. Sorry, I don't understand the formula's.

 

[StephenCrump]

Along these lines perhaps?

	A	B	C	D	E	F	G
1	Seq Length (N)	5
2						Points	Points
3			Goals	Goals		League	All
4	Date	Fixture	For	Against	Points	Sequence	Sequence
5	17 Feb 2024	FA Cup	1	2	0	-	-
6	24 Feb 2024	League	1	0	3	-	-
7	2 Mar 2024	FA Cup	1	2	0	-	-
8	9 Mar 2024	League	3	0	3	-	-
9	16 Mar 2024	League	0	0	1	-	7
10	23 Mar 2024	League	1	3	0	-	7
11	30 Mar 2024	League	0	2	0	7	4
12	6 Apr 2024	FA Cup	0	0	1	-	5
13	13 Apr 2024	League	1	4	0	4	2
14	20 Apr 2024	League	2	2	1	2	2
15	27 Apr 2024	League	0	1	0	1	2
16	4 May 2024	Charity	3	2	3	-	5
17	11 May 2024	League	1	1	1	2	5
18	18 May 2024	League	3	0	3	5	8
19
20
21		League	ALL
22	Test date	9 May 2024	9 May 2024
23	Latest available seq	27 Apr 2024	4 May 2024
24	Points	1	5
25	Previous equal or worse	#N/A	27 Apr 2024
26	Previous better	20 Apr 2024	23 Mar 2024
Sheet1

Cell Formulas
Range	Formula
E5:E18	E5	=(C5=D5)+3*(C5>D5)
F5:F18	F5	=LET(p,TAKE(FILTER(E$5:E5,B$5:B5="League",1),-N),IF(AND(B5="League",ROWS(p)>=N),SUM(p),"-"))
G5:G18	G5	=LET(p,TAKE(E$5:E5,-N),IF(ROWS(p)>=N,SUM(p),"-"))
B23	B23	=IFERROR(TAKE(FILTER(A5:A18,(A5:A18<=B22)*(F5:F18<>"-")),-1),#N/A)
C23	C23	=IFERROR(TAKE(FILTER(A5:A18,(A5:A18<=C22)*(G5:G18<>"-")),-1),#N/A)
B24:C24	B24	=XLOOKUP(B23,$A5:$A18,F5:F18)
B25	B25	=IFERROR(TAKE(FILTER(A5:A18,(A5:A18<B23)*(F5:F18<=B24)*(F5:F18<>"-")*(B5:B18="League")),-1),#N/A)
C25	C25	=IFERROR(TAKE(FILTER(A5:A18,(A5:A18<C23)*(G5:G18<=C24)*(G5:G18<>"-")),-1),#N/A)
B26	B26	=IFERROR(TAKE(FILTER(A5:A18,(A5:A18<B23)*(F5:F18>B24)*(F5:F18<>"-")*(B5:B18="League")),-1),#N/A)
C26	C26	=IFERROR(TAKE(FILTER(A5:A18,(A5:A18<C23)*(G5:G18>C24)*(G5:G18<>"-")),-1),#N/A)

Named Ranges
Name	Refers To	Cells
N	=Sheet1!$B$1	F5:G18

 

[Peter_SSs]

@StephenCrump
Stephen, I don't think the user will have TAKE in version 2021

 

[StephenCrump]

@StephenCrump
Stephen, I don't think the user will have TAKE in version 2021

Thanks Peter, I think you're right.

But please let us know first whether these formulae work for you, i.e. whether you have access to the LET, TAKE and FILTER functions?

I was being a bit lazy and asking the OP to let us know.

Let's see what does and doesn't work, and modify accordingly.

 

[Peter_SSs]

Let's see what does and doesn't work, and modify accordingly.

I just chimed in since you didn't get an answer to your earlier prompt/question.

 

[Marvo]

I do apologise, because I've recently updated from 2016 to 2021 (the excel edition is 2018) I thought I'd got the latest. I have got a lot of new functions that I didn't have previously, like LET, but I do not have the TAKE function. Again, please accept my apologies.