Excel MPH Calculation

Page may contain affiliate links. Please see terms for details.

MacB

Lover of things that come in 3's
In case anyone needs it, this has been bugging me for a while. I wanted to be able to enter my ride times in traditional format, HH:MM:SS and then have excel work out avg speed based on distance, up to now I used a workaround. However this works:-

=ROUND((Distance/(Time*86400))*3600, 2)

You need to make sure the cells are formatted correctly
 
do you not have a trip computer that tells you this info anyway? :biggrin:
 

nigelnorris

Well-Known Member
Location
Birmingham
Distance/(Time*86400))*3600 is the same as Distance/(Time*24)

So

=ROUND((Distance/(Time*24), 2)

Is prettier

Excel just stores 24:00:00 as 1 and the minutes and seconds parts as decimals thereof.
 
OP
OP
MacB

MacB

Lover of things that come in 3's
very nice Nigel, I'd picked up the 86400 from a web search, given enough time the penny may have dropped, but you never know:biggrin:

Trusty, yep computer does it but I keep an Excel sheet that records everything, trips, time, distance, bike related purchases, fare/petrol savings, my weight. Original idea was to aim to hit a personal weight target and also to break even on bike spend. The first is still achievable the second is getting less feasible by the day. If I spend nothing on bike stuff my break even is now out to about 2 years.
 

nigelnorris

Well-Known Member
Location
Birmingham
In fact to be that extra bit anal I'd format the speed cells to Number/2dp then you don't need to use the ROUND either, just

=Distance/(Time*24)

would do since the cell formatting itself would round it off for you.
 

nigelnorris

Well-Known Member
Location
Birmingham
haha :rolleyes:

Tbh it's more down to aesthetics than anything else. If you have a column of figures rounded to two dp, then one day you do exactly 18 miles in exactly one hour then your column will have that one odd value with only the whole number part showing as just 18 which looks untidy, the column won't line up unless you align left. Whereas if instead of rounding you use formatting to force two decimal digits then your 18 mph shows as 18.00 which I think looks much nicer and you can centre align the cells.
 
MacBludgeon said:
Trusty, yep computer does it but I keep an Excel sheet that records everything, trips, time, distance, bike related purchases, fare/petrol savings, my weight. Original idea was to aim to hit a personal weight target and also to break even on bike spend. The first is still achievable the second is getting less feasible by the day. If I spend nothing on bike stuff my break even is now out to about 2 years.

fair enough! i also have a spreadsheet to log my weight goals. it's not going quite as planned so far, altho i have broken under the 14st barrier for the first time in a couple of years.

i try NOT to keep too much track of bike spend. it only puts down a record of expense that my wife might see! :biggrin:
 

nigelnorris

Well-Known Member
Location
Birmingham
A slightly different tack, but I recommend AceMoney Lite as a freebie home accounts software. Easy to keep track of spending etc by categories such as bike or whatever.

Was astounded for example to find that I spent £2500 last year just in my local corner shop on groceries etc.
 
OP
OP
MacB

MacB

Lover of things that come in 3's
nigelnorris said:
A slightly different tack, but I recommend AceMoney Lite as a freebie home accounts software. Easy to keep track of spending etc by categories such as bike or whatever.

Was astounded for example to find that I spent £2500 last year just in my local corner shop on groceries etc.

oh my god, Nigel you could be more anal than me, I need to show this to my wife so that she knows I'm not a solitary freak:biggrin:
 
Top Bottom