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)

 

[Fluff]

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?

That depends on how you distinguish the individual games.

 

[SubZero55]

That depends on how you distinguish the individual games.

Could I possibly link a sheet with more data, and if possible be able to calculate the most consecutive points for the Road and Home team for each individual game?

Book1
	A	B	C	D	E
5	Game ID	Away Score	Home Score	Margin	Total
6	0021900001	0	0	0	0
7	0021900001	0	0	0	0
8	0021900001	0	0	0	0
9	0021900001	0	0	0	0
10	0021900001	2	0	-2	2
11	0021900001	2	0	-2	2
12	0021900001	2	0	-2	2
13	0021900001	2	0	-2	2
14	0021900001	2	0	-2	2
15	0021900001	2	0	-2	2
16	0021900001	2	0	-2	2
17	0021900001	2	0	-2	2
18	0021900001	2	0	-2	2
19	0021900001	2	0	-2	2
20	0021900001	2	1	-1	3
21	0021900001	2	1	-1	3
22	0021900001	2	1	-1	3
23	0021900001	4	1	-3	5
24	0021900001	4	1	-3	5
25	0021900001	4	4	0	8
26	0021900001	7	4	-3	11
27	0021900001	7	4	-3	11
28	0021900001	7	4	-3	11
29	0021900001	7	4	-3	11
30	0021900001	7	4	-3	11
31	0021900001	7	4	-3	11
32	0021900001	10	4	-6	14
33	0021900001	10	4	-6	14
34	0021900001	10	4	-6	14
35	0021900001	10	4	-6	14
36	0021900001	10	4	-6	14
37	0021900001	10	4	-6	14
38	0021900001	13	4	-9	17
39	0021900001	13	4	-9	17
40	0021900001	13	4	-9	17
41	0021900001	15	4	-11	19
42	0021900001	15	6	-9	21
43	0021900001	15	6	-9	21
44	0021900001	15	6	-9	21
45	0021900001	15	6	-9	21
46	0021900001	17	6	-11	23
47	0021900001	17	9	-8	26
48	0021900001	17	9	-8	26
49	0021900001	17	9	-8	26
50	0021900001	17	12	-5	29
51	0021900001	17	12	-5	29
52	0021900001	17	12	-5	29
53	0021900001	17	12	-5	29
54	0021900001	17	12	-5	29
55	0021900001	17	12	-5	29
56	0021900001	17	12	-5	29
57	0021900001	19	12	-7	31
58	0021900001	19	14	-5	33
59	0021900001	19	14	-5	33
60	0021900001	19	14	-5	33
61	0021900001	19	14	-5	33
62	0021900001	19	16	-3	35
63	0021900002	0	0	0	0
64	0021900002	0	0	0	0
65	0021900002	2	0	-2	2
66	0021900002	2	0	-2	2
67	0021900002	2	0	-2	2
68	0021900002	5	0	-5	5
69	0021900002	5	0	-5	5
70	0021900002	5	0	-5	5
71	0021900002	5	0	-5	5
72	0021900002	5	0	-5	5
73	0021900002	5	0	-5	5
74	0021900002	5	0	-5	5
75	0021900002	5	2	-3	7
76	0021900002	7	2	-5	9
77	0021900002	7	2	-5	9
78	0021900002	7	2	-5	9
79	0021900002	7	2	-5	9
80	0021900002	7	2	-5	9
81	0021900002	9	2	-7	11
82	0021900002	9	2	-7	11
83	0021900002	9	2	-7	11
84	0021900002	9	2	-7	11
85	0021900002	9	2	-7	11
86	0021900002	10	2	-8	12
87	0021900002	11	2	-9	13
88	0021900002	11	2	-9	13
89	0021900002	11	2	-9	13
90	0021900002	11	2	-9	13
91	0021900002	11	2	-9	13
92	0021900002	11	2	-9	13
93	0021900002	11	2	-9	13
94	0021900002	11	2	-9	13
95	0021900002	13	2	-11	15
96	0021900002	13	2	-11	15
97	0021900002	13	2	-11	15
98	0021900002	13	2	-11	15
99	0021900002	13	2	-11	15
100	0021900002	13	2	-11	15
101	0021900002	13	4	-9	17
102	0021900002	13	4	-9	17
103	0021900002	13	6	-7	19
104	0021900002	13	6	-7	19
105	0021900002	13	6	-7	19
106	0021900002	13	6	-7	19
107	0021900002	13	6	-7	19
108	0021900002	13	6	-7	19
109	0021900002	13	7	-6	20
110	0021900002	13	7	-6	20
111	0021900002	13	8	-5	21
112	0021900002	15	8	-7	23
113	0021900002	15	8	-7	23
114	0021900002	15	8	-7	23
115	0021900002	15	8	-7	23
116	0021900002	15	8	-7	23
117	0021900002	15	8	-7	23
118	0021900002	15	8	-7	23
119	0021900002	15	8	-7	23
120	0021900002	15	8	-7	23
121	0021900002	16	8	-8	24
122	0021900002	17	8	-9	25
123	0021900002	17	8	-9	25
124	0021900002	17	8	-9	25
125	0021900002	17	8	-9	25
126	0021900002	17	8	-9	25
127	0021900002	17	8	-9	25
128	0021900002	17	8	-9	25
129	0021900002	17	8	-9	25
130	0021900002	17	8	-9	25
131	0021900002	17	8	-9	25
132	0021900002	17	8	-9	25
133	0021900002	17	8	-9	25
134	21900003	0	0	0	0
135	21900003	0	0	0	0
136	21900003	0	0	0	0
137	21900003	0	0	0	0
138	21900003	0	0	0	0
139	21900003	0	0	0	0
140	21900003	0	0	0	0
141	21900003	0	0	0	0
142	21900003	0	0	0	0
143	21900003	0	0	0	0
144	21900003	0	0	0	0
145	21900003	2	0	-2	2
146	21900003	2	0	-2	2
147	21900003	2	0	-2	2
148	21900003	4	0	-4	4
149	21900003	4	0	-4	4
150	21900003	4	0	-4	4
151	21900003	4	2	-2	6
152	21900003	4	2	-2	6
153	21900003	4	2	-2	6
154	21900003	4	2	-2	6
155	21900003	4	2	-2	6
156	21900003	4	5	1	9
157	21900003	6	5	-1	11
158	21900003	6	5	-1	11
159	21900003	6	5	-1	11
160	21900003	6	5	-1	11
161	21900003	6	5	-1	11
162	21900003	6	7	1	13
163	21900003	6	7	1	13
164	21900003	6	7	1	13
165	21900003	6	10	4	16
166	21900003	6	10	4	16
167	21900003	6	10	4	16
168	21900003	6	10	4	16
169	21900003	6	10	4	16
170	21900003	6	10	4	16
171	21900003	6	10	4	16
172	21900003	6	10	4	16
173	21900003	6	10	4	16
174	21900003	6	10	4	16
175	21900003	6	10	4	16
176	21900003	7	10	3	17
177	21900003	7	10	3	17
178	21900003	8	10	2	18
179	21900003	8	10	2	18
180	21900003	8	13	5	21
181	21900003	10	13	3	23
182	21900003	10	16	6	26
183	21900003	10	16	6	26
184	21900003	10	16	6	26
185	21900003	10	16	6	26
186	21900003	12	16	4	28
187	21900003	12	16	4	28
188	21900003	12	16	4	28
189	21900003	12	16	4	28
190	21900003	12	16	4	28
191	21900003	12	16	4	28
192	21900003	12	16	4	28
193	21900003	12	16	4	28
194	21900003	12	16	4	28
195	21900003	12	16	4	28
196	0021900004	0	0	0	0
197	0021900004	0	0	0	0
198	0021900004	0	0	0	0
199	0021900004	0	0	0	0
200	0021900004	0	0	0	0
201	0021900004	0	0	0	0
202	0021900004	0	0	0	0
203	0021900004	0	0	0	0
204	0021900004	0	0	0	0
205	0021900004	0	0	0	0
206	0021900004	0	0	0	0
207	0021900004	0	0	0	0
208	0021900004	0	0	0	0
209	0021900004	0	0	0	0
210	0021900004	0	0	0	0
211	0021900004	0	0	0	0
212	0021900004	0	0	0	0
213	0021900004	0	0	0	0
214	0021900004	0	0	0	0
215	0021900004	0	0	0	0
216	0021900004	0	0	0	0
217	0021900004	0	0	0	0
218	0021900004	0	0	0	0
219	0021900004	2	0	-2	2
220	0021900004	2	0	-2	2
221	0021900004	2	0	-2	2
222	0021900004	4	0	-4	4
223	0021900004	4	0	-4	4
224	0021900004	4	0	-4	4
225	0021900004	6	0	-6	6
226	0021900004	6	0	-6	6
227	0021900004	6	2	-4	8
228	0021900004	6	2	-4	8
229	0021900004	6	4	-2	10
230	0021900004	6	4	-2	10
231	0021900004	6	4	-2	10
232	0021900004	6	4	-2	10
233	0021900004	6	6	0	12
234	0021900004	9	6	-3	15
235	0021900004	9	6	-3	15
236	0021900004	9	6	-3	15
237	0021900004	9	6	-3	15
238	0021900004	9	6	-3	15
239	0021900004	9	6	-3	15
240	0021900004	9	6	-3	15
241	0021900004	9	6	-3	15
242	0021900004	11	6	-5	17
243	0021900004	11	6	-5	17
244	0021900004	11	6	-5	17
245	0021900004	11	6	-5	17
246	0021900004	11	6	-5	17
247	0021900004	11	6	-5	17
248	0021900004	13	6	-7	19
249	0021900004	13	6	-7	19
250	0021900004	13	7	-6	20
251	0021900004	13	7	-6	20
252	0021900004	13	7	-6	20
253	0021900004	13	7	-6	20
254	0021900004	13	7	-6	20
255	0021900004	13	7	-6	20
256	0021900004	13	7	-6	20
257	0021900004	13	9	-4	22
258	0021900004	13	9	-4	22
259	0021900004	13	11	-2	24
260	0021900004	13	11	-2	24
261	0021900004	13	11	-2	24
262	0021900004	13	11	-2	24
263	0021900004	13	11	-2	24
264	0021900004	13	11	-2	24
265	0021900004	13	11	-2	24
266	0021900004	13	11	-2	24
267	0021900004	13	11	-2	24
268	0021900004	14	11	-3	25
269	0021900004	14	11	-3	25
270	0021900004	15	11	-4	26
271	0021900004	15	13	-2	28
272	0021900004	17	13	-4	30
273	0021900004	17	13	-4	30
274	0021900004	17	15	-2	32
275	0021900004	19	15	-4	34
276	0021900004	19	17	-2	36
277	0021900004	19	17	-2	36
278	0021900004	19	17	-2	36
279	0021900004	19	17	-2	36
280	0021900004	19	17	-2	36
281	0021900004	19	17	-2	36
282	0021900004	22	17	-5	39
283	0021900004	22	19	-3	41
284	0021900004	22	19	-3	41
285	0021900004	22	19	-3	41
286	0021900004	22	19	-3	41
287	0021900004	24	19	-5	43
288	0021900004	24	19	-5	43
289	0021900004	24	19	-5	43
290	0021900004	24	19	-5	43
291	0021900004	25	19	-6	44
292	0021900004	26	19	-7	45
293	0021900004	26	19	-7	45
294	0021900004	26	19	-7	45
295	0021900004	26	19	-7	45
296	0021900004	26	19	-7	45
297	0021900004	27	19	-8	46
298	0021900004	27	22	-5	49
299	0021900004	27	22	-5	49
300	0021900004	27	24	-3	51
301	0021900004	27	24	-3	51
302	0021900004	27	24	-3	51
303	0021900004	27	24	-3	51
304	0021900004	27	24	-3	51
305	0021900004	27	24	-3	51
306	0021900004	27	24	-3	51
307	0021900004	27	24	-3	51
308	0021900004	27	24	-3	51
309	0021900004	27	24	-3	51
310	0021900004	27	24	-3	51
311	0021900004	27	26	-1	53
312	0021900004	29	26	-3	55
313	0021900004	29	26	-3	55
314	0021900004	29	26	-3	55
315	0021900004	29	26	-3	55
316	0021900004	29	26	-3	55
317	0021900004	29	26	-3	55
318	0021900004	29	28	-1	57
319	0021900004	32	28	-4	60
320	0021900004	32	31	-1	63
321	0021900004	32	31	-1	63
322	0021900004	32	31	-1	63
323	0021900004	32	31	-1	63
324	0021900004	32	31	-1	63
325	0021900004	32	31	-1	63
326	0021900004	32	31	-1	63
327	0021900004	32	31	-1	63
328	0021900004	32	31	-1	63
329	0021900004	32	31	-1	63
330	0021900004	32	31	-1	63
331	0021900004	32	31	-1	63
332	0021900004	33	31	-2	64
333	0021900004	33	31	-2	64
334	0021900004	33	31	-2	64
335	0021900004	34	31	-3	65
336	0021900004	34	31	-3	65
337	0021900004	34	31	-3	65
338	0021900004	34	31	-3	65
339	0021900004	34	31	-3	65
340	0021900004	34	31	-3	65
341	0021900004	34	31	-3	65
342	0021900004	34	31	-3	65
343	0021900004	34	31	-3	65
344	0021900004	34	33	-1	67
345	0021900004	34	33	-1	67
346	0021900004	34	33	-1	67
347	0021900004	34	33	-1	67
348	0021900004	34	33	-1	67
349	0021900004	34	33	-1	67
350	0021900004	34	33	-1	67
351	0021900004	34	33	-1	67
352	0021900004	36	33	-3	69
353	0021900004	36	33	-3	69
354	0021900004	36	33	-3	69
355	0021900004	36	33	-3	69
356	0021900004	36	33	-3	69
357	0021900004	36	33	-3	69
358	0021900004	36	33	-3	69
359	0021900004	36	35	-1	71
360	0021900004	38	35	-3	73
361	0021900004	38	37	-1	75
362	0021900004	38	37	-1	75
363	0021900004	38	37	-1	75
364	0021900004	40	37	-3	77
365	0021900004	40	37	-3	77
366	0021900004	40	37	-3	77
367	0021900004	42	37	-5	79
368	0021900004	42	40	-2	82
369	0021900004	44	40	-4	84
370	0021900004	44	40	-4	84
371	0021900004	44	40	-4	84
Sheet1

Cell Formulas
Range	Formula
D6:D371	D6	=C6-B6
E6:E371	E6	=B6+C6
A6:A62	A6	="0021900001"
A63:A133	A63	="0021900002"
A196:A371	A196	="0021900004"

Cheers!

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
5	Game ID	Away Score	Home Score	Margin	Total				Away	Home
6	0021900001	0	0	0	0			0021900001	11	6
7	0021900001	0	0	0	0			0021900002	8	6
8	0021900001	0	0	0	0			21900003	2	5
9	0021900001	0	0	0	0			0021900004	2	5
10	0021900001	2	0	-2	2
11	0021900001	2	0	-2	2
12	0021900001	2	0	-2	2
13	0021900001	2	0	-2	2
14	0021900001	2	0	-2	2
15	0021900001	2	0	-2	2
16	0021900001	2	0	-2	2
17	0021900001	2	0	-2	2
18	0021900001	2	0	-2	2
19	0021900001	2	0	-2	2
20	0021900001	2	1	-1	3
21	0021900001	2	1	-1	3
22	0021900001	2	1	-1	3
23	0021900001	4	1	-3	5
24	0021900001	4	1	-3	5
25	0021900001	4	4	0	8
26	0021900001	7	4	-3	11
27	0021900001	7	4	-3	11
28	0021900001	7	4	-3	11
29	0021900001	7	4	-3	11
30	0021900001	7	4	-3	11
31	0021900001	7	4	-3	11
32	0021900001	10	4	-6	14
33	0021900001	10	4	-6	14
Master

Cell Formulas
Range	Formula
D6:D33	D6	=C6-B6
E6:E33	E6	=B6+C6
H6:H9	H6	=UNIQUE(FILTER(A6:A600,A6:A600<>""))
I6:I9	I6	=LET(Fltr,FILTER($B$6:$C$600,($A$6:$A$600=H6)*($B$6:$B$600>0)),Most,(FILTER(INDEX(Fltr,,1),INDEX(Fltr,,2)=MODE.SNGL(INDEX(Fltr,,2)))),MAX(Most)-MIN(Most))
J6:J9	J6	=LET(Fltr,FILTER($B$6:$C$600,($A$6:$A$600=H6)*($C$6:$C$600>0)),Most,(FILTER(INDEX(Fltr,,2),INDEX(Fltr,,1)=MODE.SNGL(INDEX(Fltr,,1)))),MAX(Most)-MIN(Most))
A6:A33	A6	="0021900001"
Dynamic array formulas.