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
Joined
Oct 28, 2007
Messages
9
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
 
Joined
Oct 20, 2004
Messages
7,837
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?
 
Joined
Oct 29, 2007
Messages
10
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
 
Joined
Jul 25, 2004
Messages
5,458
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
Joined
Oct 28, 2007
Messages
9
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
 
Joined
Oct 20, 2004
Messages
7,837
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.
 

outsider2832

Thread Starter
Joined
Oct 28, 2007
Messages
9
Thanks for the adjustments, this looks great.

Thank you for your trouble, very much appreciated.

Outsider2832
 
Joined
Jul 25, 2004
Messages
5,458
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
Joined
Oct 28, 2007
Messages
9
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
Joined
Oct 28, 2007
Messages
9
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
 
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
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.

Join over 807,865 other people just like you!

Latest posts

Members online

Top