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

[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

 

[Bob Phillips]

If A2 holds the time, try

=N(60/(A2*24*60))

 

[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

 

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