# Excel - Time calaculations

Discussion in 'Business Applications' started by outsider2832, Oct 28, 2007.

Thread Status:
Not open for further replies.
Advertisement
1. ### outsider2832Thread 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

2. ### slurpee55

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?

3. ### kirbs_ph

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

4. ### outsider2832Thread Starter

Joined:
Oct 28, 2007
Messages:
9
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

File size:
28.5 KB
Views:
105
5. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
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?

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

7. ### slurpee55

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.

8. ### kirbs_ph

Joined:
Oct 29, 2007
Messages:
10
modified you file a bit... hope this helps. i just used formulas... am not to adept with macros and codes

File size:
36 KB
Views:
73
9. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
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.

File size:
32 KB
Views:
98
10. ### outsider2832Thread Starter

Joined:
Oct 28, 2007
Messages:
9
Thank you for your trouble, but don't seem to be able to access file.

Outsider2832

11. ### outsider2832Thread Starter

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

Thank you for your trouble, very much appreciated.

Outsider2832

12. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
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??

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

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

15. ### outsider2832Thread Starter

Joined:
Oct 28, 2007
Messages:
9
Thanks,

Mod Edit: email address deleted.

Much appreciated

Outsider2832

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/644830