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

 

[jasonb75]

There are most likely other ways to do it, this is the first thing that came to mind.

Book1
	A	B	C	D	E	F	G	H	I	J	K
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	7
5	1	A	4	TRUE	8			3	8
6	1	A	5	TRUE	10			4	8.5
7	1	A	6	TRUE	6			5	9
8	1	A	7	FALSE	7			6
Sheet1

Cell Formulas
Range	Formula
I3:K8	I3	=IF(COUNTIFS($B$2:$B$8,I$2,$D$2:$D$8,TRUE)<$H3,"",AVERAGE(AGGREGATE(15,6,$E$2:$E$8/$D$2:$D$8/($B$2:$B$8=I$2),SEQUENCE($H3))))

 

[Johhny]

Thank you for a quick reply .

But is seems that there is something wrong:

• there were not 4 nor 5 cons. valid laps in the example date, while your formula gives a result here
• formula outcome for "Best of 2" seems incorrect. Best of 2 cons. valid laps is 8 (lap num 4 and lap num 5)

I think that this formula does not take into account that laps must be made in a row in order to be taken as a series ouf of which best average is taken.

 

[jasonb75]

By consequential lap I mean a series of consequential valid laps.

So you actually mean 'consecutive' laps? Consequential has a completely different meaning, with the way your post was laid out it appeared to be clarifying the TRUE / FALSE criteria.

I think it should be possible, might take a while to find something that works though.

edit:-

Will the data be sorted by session, then driver, then lap, or can it be mixed up? Sorted should be possible, mixed I'm not so sure about.

 

[Johhny]

Yes, consecutive. Sorry for confusion.

 

[jasonb75]

Not a problem, could you also check the edit that I made to my previous reply and clarify please.

 

[Johhny]

Will the data be sorted by session, then driver, then lap, or can it be mixed up? Sorted should be possible, mixed I'm not so sure about.

Actualy data will be sorted by date and time of each lap, so it will be mixed, but it is not a problem to sort this data by session, then driver, then lap.

 

[jasonb75]

Deleted reply, serious error in results.

 

[jasonb75]

ignore that reply, as soon as I submitted it I noticed that the results were wrong, back to the drawing board.

 

[jasonb75]

@Alex Blakenburg do you think that what is needed here could be done with a dictionary? After a few failed attempts with native formulas / functions, I'm starting to think that alternative methods may be required.