Weighted Average

[Metcalfe]

I have specific speed data for a route broken down into specific sections e.g. Point 1 to Point 2: 100m/hr, Point 2 to Point 3: 200m/hr.

The route has since been revised therefore the sections have been updated e.g. Point 1 to Point 1.5, Point 1.5 to Point 2.5, Point 2.5 to Point 3.

However, speed data has not been revised so I must apply weighted averaging to give each revised section a new speed. For example, using the revised data above, I would now have:

Point 1 to Point 1.5 100m/hr (as point 1 to point 1.5 fall within the original breakdown therefore speed designation remains the same)
Point 1.5 to Point 2.5: 150m/hr (weighted average of original section speed)
Point 2.5 to Point 3: 200m/hr (as point 2.5 to point 3 fall within the original breakdown therefore speed designation remains the same)

I would like to apply a formula in my dataset to calculate the new weighted average speed throughout. Can anyone help me with this?

Please see attached spreadsheet which indicates the Original Dataset with original route breakdown and speed and the New Dataset with new route breakdown.

I have also included a diagram as it is difficult to explain.

Capture.JPG

Thanks for any help.

[Bernie Deitrick]

Sorry - I needed to change this after my initial post. The average speed in the middle section is not 150 - it is 133, since speeds are not directly averagable - for example, if your speed for 1 mile is 1 mph, and your speed for the second mile is 60, then the average is not 30.5 - it takes you an hour and one minute to travel 2 miles, for an average speed of 2/(1+ 1/60), or 1.96 mph.

In cell I6 (the time of the first segment):

=(VLOOKUP(F6,A:B,2)-F6)/VLOOKUP(F6,A:D,4)

In cell J6 (the time of the second segment):

=(G6-VLOOKUP(F6,A:B,2))/VLOOKUP(G6,A:D,4)


In cell K6 (the average speed):

=H6/(I6+J6)

Copy those down - not sure how you want to handle text values and data outside the original data set..... And I should note that your segments for the second set cannot include more than one speed change.

[Metcalfe]

Thankyou very much that is extremely useful.