average on time cells, range definite with loops

[Astrid]

Hi,

below is an example of what my worksheet looks like: (imagine it with 2400 rows)


ID Laps Car Driver S1 S2 S3 Speed Lap Pit
800 19 72 GOUESLARD Patrice 31.928 41.336 36.363 262 1:49.628 1
839 20 72 GOUESLARD Patrice 32.341 41.564 36.430 259 1:50.335 1
871 21 72 GOUESLARD Patrice 32.504 41.487 37.878 259 1:51.870 1
907 22 72 GOUESLARD Patrice 32.264 43.869 51.863 260 2:07.997 1
959 23 72 CLAIRAY Yann 48.251 41.653 35.598 262 3:05.502 2
993 24 72 CLAIRAY Yann 31.711 42.071 36.338 262 1:50.120 2
1030 25 72 CLAIRAY Yann 31.914 41.969 35.476 255 1:49.360 2

It is the document as it is given to me by the time keeping guys after the races.
It shows the lap number, the car number, the driver's name, the times in ss.ms for the 3 sectors (the track in virtually cut in 3), the maximum speed achieved on the lap, the lap time in mm:ss.ms and the number of the stint ( a stint is when the car is running in between stops)

I am trying to make automatic the analysis of the above data (more than 2400 rows for a 6h hour race, the 24 hours of Le Mans are next month...)


77 1 1-38 LIETZ Richard best
avr green

2 39-78 LIEB Marc best
avr green

3 79-117 LIETZ Richard best
avr green

4 118-153 LIEB Marc best
avr green

5 154-187 LIETZ Richard best
avr green

6 best
avr green


Above is what I have in my analysis worksheet.
The macros I already have give me the 6 first cars in the race classification, starting with the winner (above 77) then for each stint number it puts the number of the laps and the driver who was driving the car at the time.
All that works well

Now I want to write a macro that average the each of the sector times, speed and lap times for each stint by each car. And also finds the max speed and best times.

So the macro has to first find the right car, then find the right stint number. From that it needs to find the first and last lap in the stint, do the average and best and put the data in the according cell in the analysis worksheet.

I have tried lots of things but none of them really work. Now my code is all messed up and I cannot manage to fix it.

Here it is:

Please Login or Register  to view this content.

Do you have any ideas how to fix it or do it otherwise?

Many thanks in advance!!

(I hope I formated the code properly in the thread...)

[StephenR]

Yes you have formatted your code properly, which is more than some manage. However, what would be even better would be for you to attach a workbook with a small sample of your data and desired results.

[Astrid]

ok, thanks for answering.

I have attached the full workbook I use.
It contains race data in the first worksheet (Race Results) and the second one is where I do my analysis (Stint Analysis), the one with all the macros

I used macros for each kind of data to display and each of them is triggered by a button placed at the top of the column.

To find a car, each bit of code is referring to a small table in the Race Results. I put the final race classification there manually and then the code looks for the according number in the row data which gives the cars data in car number order (72, 76, 77, ...) (I did this because it is difficult to make the search automatic: the first car completed more laps than the others but if two cars are in the same finish lap then the length of the race is used but pit stop times are not displayed here so incalculable)

I hope it will help

[StephenR]

A couple of thoughts.

One, have you considered pivot tables? The formatting may not be perfect but they should give you the basic data.

Two, could you add to your attachment the results you expect, at least for one team, as I don't fully understand everything you're doing?

[Astrid]

well I don't know what pivot tables are..... :S

for the expected results, I have attached the original file with no macros at all. The aim of the file attached before is to get the same worksheets and data all with macros.

For our subject, only consider the 2 first sheets: the first one is the row data and the second one the analysis one.
the values in the second sheet are only copies of calculations made on the first one, calculations I want to make automatic with a macro.

please tell me what you think!!
Could you explain the pivot table thing as well please?

Cheers!

[Astrid]

anyone to help me on the pivot table method that could be the answer to my problem????

many thanks in advance!!