Excel - Time calaculations

[outsider2832]

Hi,

I use Excel at my sailing club to record the times for the sailed laps.

Some boats are faster than others, maximum number of laps are 6.

I need to write a formula or macro (never made a macro before!) that will provide the time for boats that have done all six laps, only three, or four or five.

My spread sheet has a total time column in sceonds, calculated for the six individual time columns recording the time for each boat.

Currently I have to "adjust" the total column by hand by specifing the column with the last time in it.

I would like to be able to write something that will give the following result:

If (f6<g6 then g6) If (g6<h6 then h6) If (h6<i6 then i6) If (i6<j6 then j6) If (j6<k6 then k6)

Column f6 is first lap time, column g6 is second lap time, column h6 is third lap time etc.

If a boat only completes three laps the remaing three laps will be shown as 0:00:00 time.

Total column is written in seconds for handicap calculations.

Any help gratefully received

Outsider2832

 

[slurpee55]

if you could post your file with some examples of data it would be easier to work this out. Thanks. It sounds like you have something like
Lap1 Lap2 Lap3 Lap4 Lap5 Lap6
timeL1 timeL2 timeL3 timeL4 timeL5 timeL6
is that right?

 

[kirbs_ph]

you could use the Max() and Min() function of excel =MAX(F6:K6)-MIN(F6:K6) just leave the other laps that were not completed blank instead of 0:00:00

 

[outsider2832]

Thank you for your interest, I hope that the attached Excel worksheet will demonstrate the difficulty that I have.

See Excel worksheet WINTER POINTS 3 28.10.2007 attachment

 

[Zack Barresse]

Do you want to track laps 3 or 4 even if the boat has finished 6 laps? Also, would you mind us proposing a slightly different layout?

 

[outsider2832]

Dear Firefytr,

Thanks for your interest, I would like to have the time (in seconds) in Elapsed Time (column L6) updated as each lap is completed, ie as time for lap one is entered in lap 1 column it appears in the Elapsed Time column (L6), when lap two time is entered the Elapsed Time column is updated. This would save time at the end of the race going back over the laps changing the formula in column L6 to take account of the laps completed.

At the moment setting a formula in L6 to cover the last lap does not take into account the different boat handicaps - fast boats will complete six laps, slow boats will only complete two or three.

Any help will be gratefully received.

Outsider

 

[slurpee55]

do you have a column indicating the type/speed of boat - coded A, B, C or 1, 2, 3 or whatever? I could see that being useful in devising a formula.

 

[kirbs_ph]

modified you file a bit... hope this helps. i just used formulas... am not to adept with macros and codes

 

[Zack Barresse]

I'm not sure about the formula in column U, but I took out all the extra rows, added a few extra columns and formulas accordingly, plus a ranking structure (non-unique, non-sorted). Let us know what you think.

 

[outsider2832]

Thank you for your trouble, but don't seem to be able to access file.

Outsider2832

 

[outsider2832]

Thanks for the adjustments, this looks great.

Thank you for your trouble, very much appreciated.

Outsider2832

 

[Zack Barresse]

If you cannot click the file link I posted, send me a pm with your email address and I'll send it directly to you.

Edit: So you're good then??

 

[outsider2832]

Thanks, the column annotated "handicap" (Column 4) is the boat theoretical speed.

For a handicap race the "Elapsed Time" is divided by the handicap (for the Mercury that would be 1.090 - ie 4550/1.090 = 4174). This means that slow boats have a "chance" of beating faster boats - makes the faster boats work harder!

Thank you for your continued interest.

Outsider2832

 

[outsider2832]

Thank you for your continued interest.

I am constrained in the size of the workbook by the ability to print the results - A4 landscape. This is why each competitor has a main row and an "note" row below, which shows the individual lap time for each lap, rather that the running "clock" time on which the results are based. The clock is started at the beginning of the race and does not stop until the last boat crosses the line.

I very much appreciate you help and interest in my problem

Outsider2832

 

[outsider2832]

Thanks,

Mod Edit: email address deleted.

Much appreciated

Outsider2832