Average speed

[jon]

Hi all
Having trouble working out average speed.

miles in a1 =14.3
time in B1 = 0:50:23 this cell has been formatted to HH:MM:SS

The formula A1/B1 brings back the wrong answer if I alter to A1/(B1*24)
still does not work correctly.

Advise please.

Thanks

Jon

 

[Jean Ruch]

Hi all
Having trouble working out average speed.

miles in a1 =14.3
time in B1 = 0:50:23 this cell has been formatted to HH:MM:SS

The formula A1/B1 brings back the wrong answer if I alter to
A1/(B1*24) still does not work correctly.

Advise please.

Thanks

Jon

Jon,

Probably formatting the answer as a number will solve your problem

regards

Jean

 

[Sandy Mann]

still does not work correctly.
It would have been better if you had said what result you got.

Did you re-format the result cell as General?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[T. Valko]

What result do you expect?

If you want the average time per mile you should divide B1 by A1:

=B1/A1

Result = 0:03:31

Biff

 

[jon]

Hi

some figures I have from the spread sheet are

distance 23.44 miles
time 1:41:59
Average speed 13.7 MPH

the result I get is 23:19:17 not the 13.7 it should be.

I want to check the aververage speed after a calculation which alters the
time and distance

Jon

 

[jon]

Sorted

I had some formatting in the cell I did not know about and had to times the
time by 24

Thanks everyone

Jon

 

[Dana DeLouis]

I had some formatting in the cell I did not know about

Just two cents. Most likely, when you entered =A1/B1, Excel was "helping"
you by automatically formatting the cell to the same Time Format as cell B1.
I'm sure you know, but the time in B1 is a fraction of a day, so the formula
=A1/B1 is actually Miles/Day. One divides by 24 to get Miles/Hour.
A custom format (or style) of #.## "mph" can sometimes be helpful.