Trouble with time formats

[Mark Ambrose]

Any help appreciated!

I need to work out the average speed it would take to cover a certain
distance.

EG.

If you covered 10 miles in 28 minutes what would teh average speed be...

Column A: 10 miles
Column B: 28 minutes
Column C: Would return 21.43mph.

I then want to to add time increments of say 15 seconds in rows so that I
can see that if it took 28 minutes, 45 seconds, the average speed column
would return 20.87mph.

I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s),
but I want to format it as time, i.e: 00:28:45 (or 01:10:30)

Is this possible?

Thanks in advance.

Mark

 

[J.E. McGimpsey]

XL stores times as fractional days (e.g. 3:00:00 = 0.125), so to get
hours, you need to multiply by 24:

A1: 10
B1: 00:28:00
C1: =A$1/(B1*24)

then

B2: =B1 + TIME(0,0,15)
C2: =A$1/(B2*24)

Copy B2 and C2 down as far as desired.

 

[MSA]

XL stores times as fractional days (e.g. 3:00:00 = 0.125), so to get
hours, you need to multiply by 24:

A1: 10
B1: 00:28:00
C1: =A$1/(B1*24)

then

B2: =B1 + TIME(0,0,15)
C2: =A$1/(B2*24)

Copy B2 and C2 down as far as desired.

Excellent work, thank you.

Mark