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.
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?
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.
The basic formula for an average is to sum a list of entries and divide by the number of entries. ??
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
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
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.
-
Course of the Month4 days, 18 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
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