average on part of a column in VBA Excel 2007

[Guest]

Hi,

I am trying to average an unknown number of values in the same column.
The data is the result from a race and so I have a column with the
number of the lap, then the number of the car, then the driver's name
then the lap times then a last column with the number of pit stop (1
for the first, 2 for the second of this car, etc)

So I want VBA to look for a specific car in my worksheet and then to
average the laptimes in between say pit stop 1 and the row before pit
stop 2 in another cell in another worksheet but I cannot manage to do
the average.

(Find the rows with the right car number i know how to do though).

Could you please help me?
Many thanks in advance

 

[Bernie Deitrick]

astrid,

You need to better describe your data structure - and post at least one row's worth of example data
(with headers, and column addresses), with your expected result.

HTH,
Bernie
MS Excel MVP

 

[astrid.triaud]

sorry... :S

ID Laps Car Driver S1 S2
S3 Speed Lap Stint
26 1 72 GP 41.668 42.792 36.396 0
2:00.857 1
66 2 72 GP 32.137 40.858 35.517 262 1:48.512
109 3 72 GP 31.811 40.681 35.299 261
1:47.791
152 4 72 GP 31.596 40.559 35.393 260
1:47.549
196 5 72 GP 31.695 40.929 35.467 257
1:48.091
238 6 72 LR 31.631 40.726 35.217 259
1:47.574 2

ok so here is the data (with a few more rows than said before but I
was trying to make it simple....)

I have about 186 laps for I think about 12 cars in my spreadsheet.
So what I want to do is some race results analysis.
Through a table I tell Excel which car finished first, second, third,
fourth and fifth.
Then I want Excel through macros to do sort out the data I want, the
way I want it and in another worksheet.

On my other worksheet I have a table in looking like that:

Team Stint Driver Sector 1 Sector 2 Sector 3 Lap Max Speed

1 best
avr green

2 best
avr green

3 best
avr green


In team, a macro puts the number of the car (in the first row, the
winner, ...): it works
In Driver, depending on the number of the car and the stint number
another macro puts the name of the driver driving.
In Stints, a third macro puts the number of the laps, for example here
with GP it 1-5

Now what I want to do for each of my five first cars (not necessarily
the five first cars in my row data spreadsheet, in the last race for
example 72 above was not in the first 5) is in Sector 1, Sector 2,
Sector 3, Lap time and Max Speed put the best time/speed achieved (in
the best line) and the average in the average line.
The best and average have to be calculated in between the little 1 and
2 in the last column of my row data.

So in the second table, Stint 1 is happening between the marks 1 and
2, Stint 2 is in between 2 and 3, ....

So I need the macro to:
1- find the rows with the right car number
2- find the little 1
3- find the little 2
4- do the average and min/max values in this range for the Stint 1
5 do it again between 2 and 3 for Stint 2
6 between 3 and 4 for Stint 3
7... until all the Stints are done

then do it for the next car

I hope it's clear enough this time....
Thank for answering the first time, I hope you can help me!
Thanks in advance

 

[astrid.triaud]

here is the first table again, I hope it won't be all messed up this
time....

ID Laps 0 Driver S1 S2 S3 Speed Lap Stint
26 1 72 GP 41.668 42.792 36.396 0 2:00.857 1
66 2 72 GP 32.137 40.858 35.517 262 1:48.512
109 3 72 GP 31.811 40.681 35.299 261 1:47.791
152 4 72 GP 31.596 40.559 35.393 260 1:47.549
196 5 72 GP 31.695 40.929 35.467 257 1:48.091
238 6 72 LR 31.631 40.726 35.217 259 1:47.574 2

 

[Bernie Deitrick]

If you fill in the Stint column instead of leaving gaps, you could simply dump all this into a pivot
table, and use Driver, Stint, etc as row or column fields, with S1, S2, S3, etc as data fields
(entered one or more times) set to MAX, MIN, AVERAGE, etc. You could add one field, to identify
which are the items that fall within your top 5 - not sure which field that is in your data set.
Then you don't need macros or formulas....

HTH,
Bernie
MS Excel MVP

 

[astrid.triaud]

thanks but I can't fill in the stint column with numbers, I have more
than 2240 rows in total! the aim of the macros and all is to make the
analysis automatic and so a lot faster than copy each data manually.
And that's for a six hour race, imagine when I will have to analyse
the data from the 24h of Le Mans!!!
the stint values only show where a stint begins and its number for a
car, so when I change car I start again at 1.

 

[astrid.triaud]

the thing is on the example the 2 comes after 6 laps but it is usually
between 30 and 40 laps in each stint.
I cannot use the change in drivers to know when stints change because
sometimes the driver double stint (by the way a stint is the period
between to pit stops, when the car runs on the track)
I cannot use the big change in lap time when the car comes in or goes
out because either because those laptimes are similar to the ones
under safety car.
So the only way I found to know where the stints are is to manually
put those 1 to 7 values for each car. But I can't put those everywhere
it would be way too long!!

Do you please know how to do it without the numbers everywhere?
Just in case could you explain with more details the method you said
about before? I don't know how to use VBA very well ( I have been on
it for two days only!) and so I don't know what you are talking about
in your answer.

Thanks again!

 

[Bernie Deitrick]

astrid,

Select your column with "Stint" Values, then select Edit / Go to...
Special Blanks OK then press = and then the up-arrow key once, and
press Ctrl-Enter. Then Copy that column and Paste Special Values. That
way, your column is full, and you can use the Pivot Table approach. If you
want, contact me privately (make the obvious changes) with an example
workbook, and I will show you what a Pivot Table can do.

Bernie