# 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?
###### Who is Participating?

x

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

Commented:
AVG() not working for you ?
0

Author 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

Author Commented:
in decimals 15/17.8333*60   50.467km/h
0

Fixer of ProblemsCommented:
The basic formula for an average is to sum a list of entries and divide by the number of entries.  ??
0

Author Commented:
not in this case
0

Finance 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

Commented:
You could simplify this, Rob

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

I'd use

=B3/C3/24

regards, barry
0

Author 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.