Time calculations don't match

cpereznj

New Member
Joined
Nov 27, 2012
Messages
19
Good morning, I'm working on a distance, speed and time calculator and came up with a discrepancy. If I take let's say 100 miles and want to find out how long it takes to travel that distance based on 20 mph I get 5 hours using this formula =IFERROR(IF(D3="","",(D3/E3)/24),""). However, if I break down the distance into segments that add up to 100 miles, I end up with 5 hours 4 minutes and 19 seconds. I am using this formula to calculate the time: =IFERROR(IF(D3="","",(D3/E3)/24),""). Why is there a 4 minute and 19 seconds difference?
 

Attachments

  • Capture.JPG
    Capture.JPG
    45 KB · Views: 11

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,429
Office Version
  1. 2016
Platform
  1. Windows
Hi CPereznj,

Because your sum row for speed is actually an average and you can't apply an average to all the rows.

e.g. take all your speeds and make them 10. Now your miles are 100, speed 10 and time 10 hours which also works on the totals.
Now change that last speed from 10 to 20 and the time for that row changes and the average speed becomes 10.91 but that doesn't apply to the previous 11 runs.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,589
Office Version
  1. 365
Platform
  1. Windows
The average in the top table is not 20mph, the lower sections are longer so it has to be weighted.

The true average speed in E14 would be =D14/(F14*24)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,021
Office Version
  1. 2010
Platform
  1. Windows
@cpereznj.... Others have noted part of the problem, namely: in general, the average of the whole does not equal the sum of the averages of the parts.

But I believe your data reflects a number of mistakes, which compounds that fundamental problem.

The root cause of the disparity between the 5:00:00 and 5:04:19 total times is the data itself.

In original data, you assume that the speeds are accurate to the integer, and you derive the times from distance and speed of each leg.

Since that assumption is probably incorrect, the derived times are incorrect. Consequently, their sum is incorrect.

Usually, we know the distance and time, and we derive the speed. This is demonstrated below.



You can see the formulas by clicking on the cells.

The times are hypothetical. I prorated your original derived times so that their sum is 5 hours, which I assume is known to be correct (J21).

Even the distances might be inaccurate as displayed, rounded to 2 decimal places. But I assume they are accurate "enough", since they do sum to 100 miles, which again I assume is known to be correct (H21).

As you can see, the speeds in I3:I13 are not integers. But note that they round to the integers in your original data. (Truth be told, that is just a coincidence.)

(Conversely, if you want to derive times that are "accurate enough", you need to know the speeds with greater accuracy.)

Now we can talk about your arithmetic errors.

As jasonb75 wrote, the actual average speed is total distance divided by total time. That is demonstrated by the formula in I14.

In general, that is not the same as a simple average of the speeds for each leg of the trip. That is demonstrated by the formula in I15.

Instead, as jasonb75 wrote, the average speed can also be calculated by a weighted average of the speeds for each leg.

But weighted by what?

The answer is: weighted by time. That is demonstrated by the formula in I16.

We cannot weight by distance. That is demonstrated by the incorrect result of the formula in I17.

The reason that we weight by time is, again: average speed is total distance divided by total time. If you work out the algebra of the weighted average in I16, you will see that speed (distance divided by time) times time results in distance. Thus, the numerator is the sum of the distances, and the denominator is the sum of the times.

As toadstool wrote, the one exception is: if each leg took the same amount of time, the simple average of the speeds would be the same as the weighted average of the speeds.

In my example, you can demonstrate that by replacing the times in J3:J13 with the formula =$J$21/11 .

(Of course, that changes the average speed of each leg.)
 
Last edited:

cpereznj

New Member
Joined
Nov 27, 2012
Messages
19
Thank you all for your feedback. It is much appreciated. I will review and update as needed.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,264
Messages
5,635,153
Members
416,844
Latest member
ryanangus496

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top