Consecutive Points Scored

[SubZero55]

Hello,

I need an Excel formula that determines the Maximum number of consecutive points scored by a particular team.

I have found threads that cover similar questions but none that can quite get the correct answer.

Any help would be greatly appreciated!

So here the most consecutive points scored by the Away team would be 11 (from 4-4 to 15-4), the most consecutive points scored by the home team would be 6 (from 6-17 to 12-17)

 

[PeteWright]

Alright, got you.

Could you provide me your table? Or some data to work with?

 

[SubZero55]

I've got a small amount of data in a new sheet, how do I upload it on to the forum? I have downloaded the XL2BB but it does not seem to activate?

 

[Fluff]

Hi & welcome to MrExcel.

I have downloaded the XL2BB but it does not seem to activate?

Did you unzip the xlam & put it in the add-ins folder, which should be somewhere like C:\Users\username\AppData\Roaming\Microsoft\AddIns

Also do you have the new LET function?

 

[SubZero55]

Book1
	J
11
Sheet1

 

[Fluff]

You need to select the range you want to post, before clicking the "Capture Range" button.

 

[SubZero55]

Hi & welcome to MrExcel.

Did you unzip the xlam & put it in the add-ins folder, which should be somewhere like C:\Users\username\AppData\Roaming\Microsoft\AddIns

Also do you have the new LET function?

Hello, thanks for your help!

The add-on is loaded now, I'll just try again capturing the information.

Cheers

 

[SubZero55]

Book1
	A	B	C	D
5	Away Score	Home Score	Margin	Total
6	0	0	0	0
7	0	0	0	0
8	0	0	0	0
9	0	0	0	0
10	2	0	-2	2
11	2	0	-2	2
12	2	0	-2	2
13	2	0	-2	2
14	2	0	-2	2
15	2	0	-2	2
16	2	0	-2	2
17	2	0	-2	2
18	2	0	-2	2
19	2	0	-2	2
20	2	1	-1	3
21	2	1	-1	3
22	2	1	-1	3
23	4	1	-3	5
24	4	1	-3	5
25	4	4	0	8
26	7	4	-3	11
27	7	4	-3	11
28	7	4	-3	11
29	7	4	-3	11
30	7	4	-3	11
31	7	4	-3	11
32	10	4	-6	14
33	10	4	-6	14
34	10	4	-6	14
35	10	4	-6	14
36	10	4	-6	14
37	10	4	-6	14
38	13	4	-9	17
39	13	4	-9	17
40	13	4	-9	17
41	15	4	-11	19
42	15	6	-9	21
43	15	6	-9	21
44	15	6	-9	21
45	15	6	-9	21
46	17	6	-11	23
47	17	9	-8	26
48	17	9	-8	26
49	17	9	-8	26
50	17	12	-5	29
51	17	12	-5	29
52	17	12	-5	29
53	17	12	-5	29
54	17	12	-5	29
55	17	12	-5	29
Sheet1

 

[Fluff]

Thanks for that, how about

=MAX(FILTER(A6:A60,B6:B60=MODE.SNGL(IF(A6:A60>0,B6:B60))))-MIN(FILTER(A6:A60,B6:B60=MODE.SNGL(IF(A6:A60>0,B6:B60))))

or if you have the LET function

=LET(Fltr,FILTER(A6:A60,B6:B60=MODE.SNGL(IF(A6:A60>0,B6:B60))),MAX(Fltr)-MIN(Fltr))

 

[SubZero55]

Thanks for that, how about

=MAX(FILTER(A6:A60,B6:B60=MODE.SNGL(IF(A6:A60>0,B6:B60))))-MIN(FILTER(A6:A60,B6:B60=MODE.SNGL(IF(A6:A60>0,B6:B60))))

or if you have the LET function

=LET(Fltr,FILTER(A6:A60,B6:B60=MODE.SNGL(IF(A6:A60>0,B6:B60))),MAX(Fltr)-MIN(Fltr))

Both have done the trick!

Thanks very much - I assume for me to apply these to the whole data set, i'll just wrap an IF function around this to get the number from each individual game?

Much appreciated!

Have a great new year