how could I calculate ave. speed from time and distance

**Peterlckwd** · 03-26-2012, 07:04 PM

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.

**daddylonglegs** · 03-26-2012, 07:09 PM

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

**Peterlckwd** · 03-28-2012, 07:11 PM

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.

**daddylonglegs** · 03-28-2012, 07:25 PM

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

**Peterlckwd** · 04-12-2012, 07:56 PM

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.