# Excel - Time calaculations

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### outsider2832

Thread Starter
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

Thread Starter
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

#### Attachments

• 28.5 KB Views: 105

#### 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

Thread Starter
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

#### Attachments

• 36 KB Views: 73

#### 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.

#### Attachments

• 32 KB Views: 98

#### outsider2832

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

Outsider2832

#### outsider2832

Thread Starter
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

Thread Starter
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

Thread Starter
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

Thread Starter
Thanks,

Mod Edit: email address deleted.

Much appreciated

Outsider2832

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 807,865 other people just like you!