mph in excel

[rawclark]

Does anyone know of a formula for calculating time from
speed and distance?
As in d=3 miles; s=45mph; t=00:04 (hh:mm). There must be
a standard formula for s. So far the best I can do is to
use 0.0007 for 60 mph; 0.000835 for 50 mph, and so on.
These were created using trial, error, and guesswork.
Isn't there a better way?

 

[rawclark]

Yes. I know that, thank you.
In journey planners, I have d. I also can hazard a
reasonably good guess at s, given the type of road (M-
way,trunk, farm track, etc.). Now from that how do I get
t in the form hh:mm if the column is set for that format?
Answer (as I said): by trial, error, and guesswork. Is
there a better way?

 

[Dana DeLouis]

If A1 has 3 (Miles), and B1 has 0:04 minutes, perhaps

=A1/(24*B1)

The "time" of 4 minutes is a percentage of a day, so you need to convert
miles per day, to miles per hour. (1 day has 24 hours)

HTH
Dana DeLouis

 

[Alan]

Does anyone know of a formula for calculating time from
speed and distance?
As in d=3 miles; s=45mph; t=00:04 (hh:mm). There must be
a standard formula for s. So far the best I can do is to
use 0.0007 for 60 mph; 0.000835 for 50 mph, and so on.
These were created using trial, error, and guesswork.
Isn't there a better way?

Hi rawclark,

I'm not sure how simple an answer you are looking for.

To calculate (s) from (d) and (t) you use:

s = d/t

You must be careful to ensure that you are using consistent units in
your calculations though.

Using your example above:

s = d/t

= 3 miles / 4 mins

= 3/4 mile per min

= 60 x 3/4 miles per hour

= 45 miles per hour


In excel (using the default time unit in excel of a day):

A1 = 3 (miles)
B1 = 45*60 (miles per day)
C1 = A1/B1 = 0.002777.... = 4mins


HTH,

Alan.

 

[Alan]

B1 = 45*60 (miles per day)

Typo - that should have been:

B1 = 45*24 (miles per day)

to read in full:


In excel (using the default time unit in excel of a day):

A1 = 3 (miles)
B1 = 45*24 (miles per day)
C1 = A1/B1 = 0.002777.... = 4mins


Sorry for any confusion!

Alan.

 

[rawclark]

You may be right but the trouble is that the 0:04 minutes
in B1 is the figure I'm trying to get to. There must be a
constant (or range of constants if that's not a
contradiction) that applied to a mileage figure will give
the time for a given speed.
E.g. 29*0.0007=00:29 (hh:mm). 29 miles@60mph takes 29
minutes. What is the formula for finding the time taken
at (say) 36mph?
With me?

 

[David Biddulph]

Hi rawclark,

I'm not sure how simple an answer you are looking for.

To calculate (s) from (d) and (t) you use:

s = d/t

You must be careful to ensure that you are using consistent units in
your calculations though.

Using your example above:

s = d/t

= 3 miles / 4 mins

= 3/4 mile per min

= 60 x 3/4 miles per hour

= 45 miles per hour


In excel (using the default time unit in excel of a day):

A1 = 3 (miles)
B1 = 45*60 (miles per day)

.... or perhaps 45*24, rather than 45*60 ?

 

[Dana DeLouis]

You may be right but the trouble is that the 0:04 minutes

in B1 is the figure I'm trying to get to.

=3/(24*45)

If formatted as time, should show 0:04.

The "speed" equation was based on "mph" in the subject line, and

"There must be a standard formula for s" in the message.

Anyway, hope this helps. :>)

Dana DeLouis

 

[rawclark]

That works just fine. Thanks for that.

-----Original Message-----



Typo - that should have been:

B1 = 45*24 (miles per day)

to read in full:


In excel (using the default time unit in excel of a day):

A1 = 3 (miles)
B1 = 45*24 (miles per day)
C1 = A1/B1 = 0.002777.... = 4mins


Sorry for any confusion!

Alan.


.

 

[Guest]

In general the formula you are looking for is 1/(YourSpeed*60)

e.g.'s 60 mph = 1/(60*24) = .000694
50 mph = 1/(50*24) = .000833
45 mph = 1/(45*24) = .000926
etc.

 

[AlfD]

Hi!

You said originally you were familiar with t=d/s.

Choose 1 mile for d and this formula becomes t=1/s.
Simple inverse proportion.
Normally, these quantities would be in hours and miles per hour.
(At least where I live...)

But if you are using "native" Excel time units, then t is in days. S
speed needs to be in miles per day.
Convert s into miles per day (s*24).

Now we have t=1/(s*24), where t is in days.

Which is what unlikekansas was saying (except in the first line wher
that pernicious 60 slipped in...).

Al

 

[Alan]

{Big Snip}

(except in the first line where
that pernicious 60 slipped in...).

Indeed!

 

[rawclark]

Many thanks to all those who helped me on this. As usual
it turned out to be dead simple when you know how ...