convertion formula for (min/mile) to (mile/hr)

R

Run 4fun

I'm tring to set up a running log on excel and I want to create a formula
that converts my road pace (minutes/ mile) to a speed (miles / hr) so I can
enter that number on my treadmill. Sounds simple but the formulas I've tried
are giving me false results. Example: Walk 1 mile in 20min gives you a pace
of 20:00:00 and a speed of 3MPH. (60/20=3), but when I make the formula I get
answers like 0.1, 72, or 0
 
R

rzink

Assuming your min/mile number is in A1 (ie. 20), your formula for miles/hour
is =60/A1. This gives you 3 Miles/Hr. The following table shows the times I
tested:

Min/Mile Miles/Hr
20 3
15 4
60 1
1 60

Don't forget to press "Yes" if this was helpful.

Thanks,
rzink
 
S

Steve Dunn

20:00:00 represents 20 hours, not 20 mins, try entering 0:20 instead, then
you'll find that Bob's formula is correct. It works because times are
stored as fractions of a day, so 00:20 is stored/calculated as the value
0.013888... (it is shown as 00:20 for human convenience) so it needs to be
multiplied by 24 (hours) and 60 (mins) to equal 20 before you do the
calculation you would expect to work, i.e. 60/20. The N() stops Excel from
automatically formatting the cell as time (because it recognises that a time
was involved in the calculation).

Once all that is understood (or if your just not interested in it), you can
simplify the formula:

=n(1/(A1*24))

HTH
Steve D.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top