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!
COURSE of the MONTH
12 days, 4 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 today
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 today
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
Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Suggested Courses
Course of the Month12 days, 4 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.