Good day
i hope you guys can help
what is the easiest Formule to work out the following senario?
average / workable info: (15km per 60min)
if you travelled for 60min, your km done is 15km
i want a formule, is the guys work example 6.25 hour, 1hour, 2.15 hours, to work out the average km they have done?
If your input is: “average / workable info: (15km per 60min)” then the output for “average (!!!) km they have done” of course is 15 km/h regardless of the time and no formula needed for that. If you are in fact searching for “to work out the absolute km they have done” then multiply 15 km/h * 6.25 h, 15 km/h * 1.00 h or 15 km/h * 2.15 h.
doesn’t make sense…
see the examples:
Time Km
01:46
09:09
04:35
02:57
03:50
04:08
02:44
02:44
03:17
02:20
03:16
00:38
00:53
01:43
00:52
02:50
01:35
00:36
01:18
isn’t there a easier formule to work out the km travelled? (good avereage is for every 60min, a good terug value is 15km)
Ok - it’s at your discretion what makes sense and what doesn’t.
do you have to convert the hours to (XX*1440) then multiply or Divide by 15?
Read your own question carefully (the format you used for time and phrasing average) and decide what makes sense
BTW. Your list is ambigous - what does it mean HH:MM or MM:SS ?
If your values are [HH]:MM then you need to =A2*24*15 to get the absolute number of kilometers (assuming you first data row starts in A2)
ok, so do you agree with this?
to work out km travelled against hours worked, do the following…
take the time worked, multiply by 15 and then devide by 60?
04:35*15/60 = 01:08
answer is 108km
that is not correct, it must be for every 60min, = 15km, so the answer must be ±60km
HH:MM that is what im working on
HH:MM = 04:35
=[04:35] * 24 * 15 = 18:00
4 hours, 35min * (16km per hour) = ??
ok, so do you agree with this?
Absolutely not - the calculcation is: 04:35*24 yields 4.5833333 hours and 4.5833333 * 15 yields: 68.75 kilometers (and a simple calc by thumb would show you that 04:35 is roughly 4 and a half hour, which is 4 * 15 + 0.5*15 = 67.5. Hence the result must be near by that value.
sorry for being an ass, and for your help
what will be the easiest Formula to work on, to work out all these different hours, whole excel sheet full?
When you write 04:35*15/60, you force the first factor to be HH:MM. This type will be forced on the result, i.e. 04:35*15/60 is also a HH:MM or roughly 1 hour, which is consistently with displayed result 01:08.
You must first get the portion of an hour corresponding to 04:35. This can be done only with a division by 01:00 to get an untyped ratio. Don’t ever think of any formatting of your wrong result because time is represented as a fractional number: 1 day = 1.0, 4 hours ~ 0.16667.
When computing on physics quantities, be always consistent with your units and apply conversions where needed.
what will be the easiest formula to type and copy in all cells to work out the correct km against hours worked?
what will be the easiest formula to type and copy in all cells
Just look at @anon73440385’s answer. Download his DistanceByTime.ods
The formulas in that file should be easy enough to understand, along with the other info in the sheet. You should be able to find out how this relates to your data. Exactly what formula works for you depends on your data layout, and where your formula(s) will be in that setting.
When we do not know exactly how your data are laid out in the spreadsheet, we cannot give you the exact formula.
Thank you for all your help…
now another Question… to work out time.
Scenario one: guys started this a shift from (A1) 20h00 and ended the shift (B1) 23h00, Excel A2 minus A1 = 03h00, which is correct
Scenario two, guys started a shift at (A2) 20h00 and ended the shift at 06h00 (B2) (the next morning), excel cant calculate? the answer must be 10h00 hours
what is the easiest formula to do the necessary conversion, to work out the hours 100%?
This deserves a full question of its own. There are also several answers on this site about it.
The simplest thing is to record a full date YYYYY-MM-DD HH:MM:SS. If the date by itself is not relevant, you format the cell (column or row) not to display the date part. Again if the date is not relevant, just write 2000-01-01 20:00 and 2000-01-02 6:00 (ISO-8601 seems to be always accepted, which eliminates locale ambiguities). No need to give the exact date; only the difference matters.
thank youVery much!!! 
now another Question…
to work out time.
Senario one: guys started this a shift from (A1) 20h00 and ended the shift (B1) 23h00, Excel A2 minus A1 = 03h00, which is correct
Senario two, guys started a shift at (A2) 20h00 and ended the shift at 06h00 (B2) (the next morning), excel cant calculate? the answer myst be 10h00 hours
what is the easiest formula to do the neccesarry convertion, to work out the hours 100%?
If you have another question ask a new question. This site is about “One question - one answer” and no discussion or individual support forum.
If the answer to the current question is helpful please consider to click the check mark (
) next to the answer. Thanks in advance …
thanks for all the help