Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Course Of The Month
6 days, 11 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
how to calculate average speed per kilometer in xls with excel time format
Posted on 2011-09-12
with what formula will i get the correct value for the average speed in the yellow coloured cell?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
2
- +2
9 Comments
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.
Active today
The basic formula for an average is to sum a list of entries and divide by the number of entries. ??
Active 1 day ago
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
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
Active 1 day ago
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
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Workbook link problems after copying tabs to a new workbook?
David Miller (dlmille)
Intro
Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month6 days, 11 hours left to enroll
724 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.