Excel Experts?

essex-commuter

Well not experts, but better than me!

Average speed.

If time is in one cell formatted as hh:mm:ss
And distance is in another formatted 0.00

What is the formula and formatting to get the correct answer.

So 00:55:00 (55 mins) to cover 18.00 (18 miles) equals: ?

jfw

If

A1 = distance (in miles)

B1 = time in hh:mm:ss

Speed (mph) = A1*(B1*24)

jzed

So put 01:00:00 in cell a1.

Your time of 00:55:00 goes in cell a2.

Distance in cell a3

in a4 do =a3/(a2/a1)

Jobs a good un.

CiB

In the Stats page I calculate Avg Speed by converting HH:MM:SS to seconds, then doing the calculation

Avg = (Dist / Seconds) * 3600 rounded to two places to give MPH..

In Excel you need to convert the time cell to a number: assume your time is in A1 you want

=(A1 * 24) * 3600 to give the time in seconds (assume this in B1, B1 must be formatted as Number)

Your distance is in C1

You can then use that in the Avg calculation
=(B1 / C1) * 3600

Obviously you'll wrap it into a nice neat two cell answer.

essex-commuter

jfw wrote:

If

A1 = distance (in miles)

B1 = time in hh:mm:ss

Speed (mph) = A1*(B1*24)

But if I decrease the time the average decreases.......

Torvid

A1 is 01:00:00
A2 is Time taken
B1 is distance

=(A1/A2)*B1

should give you 19 something...

essex-commuter

Guys, thank you!

bdave262000

essex-commuter wrote:

Well not experts, but better than me!

Average speed.

If time is in one cell formatted as hh:mm:ss
And distance is in another formatted 0.00

What is the formula and formatting to get the correct answer.

So 00:55:00 (55 mins) to cover 18.00 (18 miles) equals: ?

I think its =(60/Time)*Distance (but time should be in minutes)

jfw

A1 = distance (in miles)

B1 = time in hh:mm:ss

Speed (mph) = A1*(B1*24)

sorry brah

its speed (mph) = A1/(B1*24)