Compute MPH from Distance and Minutes:Seconds?

[(PeteCresswell)]

I'm striking out on this one.

The gross formula is MPH = Distance/Time.

But in this case, time is entered as mm:ss. e.g. 2:57 for two
minutes, fifty-seven seconds.

Seems like I have to convert that time to a decimal fraction of
an hour.

Checked Excel.Help and it sounds like AnalysisToolPack.CONVERT()
is the function to use.

Indeed, CONVERT(3, "mn", "hr") ==> .05 as expected, as does
CONVERT("3", "mn", "hr")

But how to feed the function from a column formatted as "mm:ss"?

CONVERT("3:00", "mn", "hr") ==> .0021, with similarly unexpected
results when "3:00" is in a cell formatted "mm:ss".

??

 

[JE McGimpsey]

One way:

A1: <miles>
A2: <time in minutes:seconds>

A3: =A1/A2/24

Since XL stores times in fractional days, dividing distance by time
gives distance per day. Dividing again by 24 gives distance per hour.