how to calculate average speed per kilometer in xls with excel time format

with what formula will i get the correct value for the average speed in the yellow coloured cell?
stmoritzAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
What data do you have? A sample workbook would be good.

I assume you have Distance and Time take. If so the formula would be:

=(60/(C3*1440))*B3

Where:
C3 = time taken (in true time format)
1440 = Minutes in one day, multiplied by time formta converts minutes to decimal format
B3 = Distance

So as an example:
5 Km in 10 minutes - mental arithmetic says 10 minutes for 5 km therefore multiply by 60/10 to work out how far in an hour = 30

=(60/(00:10*1440))*5 = 30 KPH

If you then have multiple lines for which you want the average, use the AVERAGE function with the range of speeds.

Thanks
Rob H
0
 
exceterCommented:
AVG() not working for you ?
0
 
stmoritzAuthor Commented:
i have no clue to use average for this, as it is not an average between a similar type of data or numbers i need.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
stmoritzAuthor Commented:
in decimals 15/17.8333*60   50.467km/h
0
 
Dave BaldwinFixer of ProblemsCommented:
The basic formula for an average is to sum a list of entries and divide by the number of entries.  ??
0
 
stmoritzAuthor Commented:
not in this case
0
 
Rob HensonFinance AnalystCommented:
By range of speeds, I mean cell range containing multiple speeds.

However, you might want effectively a weighted average.

See example below:

Dist      Time      Speed (using above formula)
5      00:10      30
6      00:20      18
8      00:15      32
12      00:10      72

            38  Average of 4 lines above

31      00:55      33.81818182
Sum of four lines and then Speed using above formula.

The second option gives a different answer. In theory the second answer is more accurate for the whole, ie if this were one journey broken down into four steps. If it were 4 journeys done by 4 different people, then the 38 is more accurate because the data is not directly related as such.

Thanks
Rob H
0
 
barry houdiniCommented:
You could simplify this, Rob

=(60/(C3*1440))*B3

I'd use

=B3/C3/24

regards, barry
0
 
stmoritzAuthor Commented:
sorry, i forgot to upload a sample sheet, but th is exactly what i've been looking for and it works prfectly! thanks to everybody!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.