1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Time calaculations

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

Thread Status:
Not open for further replies.
Advertisement
  1. outsider2832

    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
     
  2. slurpee55

    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

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

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

    Attached Files:

  5. Zack Barresse

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

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

    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

    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
     

    Attached Files:

  9. Zack Barresse

    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.
     

    Attached Files:

  10. outsider2832

    outsider2832 Thread Starter

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

    Outsider2832
     
  11. outsider2832

    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
     
  12. Zack Barresse

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

    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
     
  14. 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
     
  15. outsider2832

    outsider2832 Thread Starter

    Joined:
    Oct 28, 2007
    Messages:
    9
    Thanks,

    Mod Edit: email address deleted.

    Much appreciated

    Outsider2832
     
  16. Sponsor

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

Loading...
Thread Status:
Not open for further replies.

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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice