+ Reply to Thread
Results 1 to 5 of 5

how could I calculate ave. speed from time and distance

  1. #1
    Registered User
    Join Date
    03-12-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    how could I calculate ave. speed from time and distance

    I posted this query a few days ago but cannot now find it or any response.

    I am trying to create and tinerary and want to calculate the average speed from the calculated elapsed time.
    when I divide the miles by the elapsed time I get a spurious answer.

    Sorry, if someone has already posted a response. Couldn't find it. I am new to the forum.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: how could I calculate ave. speed from time and distance

    If you have a number of miles in A1 and a time value in B1, e.g. 85 in A1 and 2:36 (2 hours 36 minutes) in B1 then use this formula in C1

    =A1/B1/24

    format C1 as number and for my example you get 32.69 - that's the average speed in MPH
    Audere est facere

  3. #3
    Registered User
    Join Date
    03-12-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: how could I calculate ave. speed from time and distance

    Thank you Daddylonglegs.
    I trieds this and yes it works. I tried the same formula for 100 miles in 01:00 hours and got 100 mph. So far so good. But with 10 miles in 1hr I get 0.4 and 5 miles in 00:30 I get 0.2 when both these should be 10 mph.

    I think there's something really silly that I'm missing here but my brain seems to be fuddled. I did make sure that the time column was correctly fornmatted at time.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: how could I calculate ave. speed from time and distance

    The formula works OK, if you get 0.4 that probably means that the hours are actually 25:00.....but if the cell is formatted as h:mm 25:00 will display as 01:00. Try formatting the time cell as [h]:mm to display the true hours value

    see attached - in B6 if you format the cell as h:mm rather than [h]:mm it will display 01:00 but the underlying value is 25:00 so the result is 0.4
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-12-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: how could I calculate ave. speed from time and distance

    Thanks Daddylonglegs,

    I think I have it now, but still haven't mastered this website, though, but it's a great resource and seems to have a good comunity.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1