Best lap time of N consequential laps

[Johhny]

Hi!

I stucked and I am not sure whether this could be resolved in excel. Can you help?

I have a dataset (attached example) of lap times:

• each record represents one lap time
• there might be many drivers
• there might be many sessions (occasions on which laps were taken)
• each lap might be valid or invalid

My aim is to calculate best average lap time of N cosequential laps for each driver our of all sessions. By consequential lap I mean a series of consequential valid laps.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Session	Driver	LapNum	ValidLap	Time				Driver
2	1	A	1	TRUE	10			Best of:	A	B	C
3	1	A	2	TRUE	11			1	6
4	1	A	3	FALSE	9			2	8
5	1	A	4	TRUE	8			3	8
6	1	A	5	TRUE	10			4
7	1	A	6	TRUE	6			5
8	1	A	7	FALSE	7
9
Sheet1

Cell Formulas
Range	Formula
I5	I5	=SUM(E5:E7)/3

 

[StephenCrump]

	A	B	C	D	E	F	G	H	I
1	Session	Driver	LapNum	ValidLap	Time		Session	1	2
2	1	A	1	TRUE	10		Driver	A	A
3	1	A	2	TRUE	11		Best of
4	1	A	3	FALSE	9		1	6	3
5	1	B	1	TRUE	7		2	8	5.5
6	1	A	4	TRUE	8		3	8	6.7
7	1	A	5	TRUE	10		4	n/a	7.25
8	1	A	6	TRUE	6		5	n/a	n/a
9	1	A	7	FALSE	n/a
10	2	A	1	TRUE	5
11	2	A	2	TRUE	6
12	2	A	3	TRUE	9
13	2	A	4	TRUE	9
14	2	A	5	FALSE	9
15	2	A	6	TRUE	3
16	2	C	1	TRUE	9
Sheet4

Cell Formulas
Range	Formula
H4:I8	H4	=LET(B,99999,t,FILTER($D$2:$E$16,($A$2:$A$16=H$1)*($B$2:$B$16=H$2)),s,SEQUENCE(ROWS(t))+SEQUENCE(,$G4,0),x,MIN(MMULT(IFERROR(INDEX(t,s,2)/INDEX(t,s,1),B),SEQUENCE($G4,,1,0))),IF(x<B,x/$G4,"n/a"))

 

[Johhny]

Many thanks. It works like a charm!

 

[StephenCrump]

You're welcome.

I didn't test it rigorously, so I suggest you check it works under the various scenarios you might realistically encounter.