What is the average speed of this road trip? [Solution & Discussion]

Last week, we had an interesting homework problem – What is the average speed of this road trip?

We received more than 150 answers. But to my surprise, 57 of them are wrong. So today, lets learn how to calculate the average speed correct way.

Please click here to download solution workbook.

What is Speed?

Back in school days, we learned what speed is.

Speed = Distance / Time

Now lets look at the problem

Here is the data:

Jack kept track for every 50 mile interval. And he did that for 12 intervals. So the total distance is 12×50 = 600 miles.

We just need to know how much time Jack took to cover the 600 miles to calculate the speed.

We know that Jack covered first 50 miles @ 43mph.

So the time taken for first 50 miles is 50/43 = 1.16 hrs (or 1 hr & 9 mins)

Like wise, if we calculate times taken for all the 50 miles, we get this:

Isn’t there a way to calculate this without helper column?

Now you are talking.

While the detailed break-up of the calculation above helps us understand how Time, Speed & Distance are related, when answering a question like “What is Jack’s average speed?”, you may want to write a single formula to get the answer (instead of all the extra helper column cells).

The range A2:A13 contains speeds per 50 mile intervals.

The time taken for first 50 mile is =50/A2

The time taken for second 50 mile is = 50/A3

…

So, 50 / (A2:A13) should give us an array of times.

And the total time taken is a simple sum of this array.

So, SUM(50/(A2:A13)) should give the total time.

Now, if we divide 600 by this, we should get our average speed for the entire trip.

Formula #1: Array SUM()

Our first formula for calculating average speed is,

=600 / SUM(50/(A2:A13))

Since this is an array formula, you should press CTRL+Shift+Enter to get it work.

Formula #2: SUMPRODUCT

While the above formula works beautifully, it is a bummer that we must press CTRL+Shift+Enter to get it work. Why not use a formula that can natively process arrays.

Enter SUMPRODUCT.

=600 / SUMPRODUCT(50/(A2:A13))

works just as beautifully and you don’t have to press CSE.

Formula #3: Harmonic Mean

Lets expand the formula and see what is happening, mathematically speaking.

Our formula is,

=600 / SUM ( 50 / (A2:A13) )

in mathematical terms, this is,

= 600 / ? [ 50 / (A2:A13) ]

= 600 / [50/A2 + 50/A3 + 50/A4 + … + 50/A13]

After isolating 50, we get:

= 600 / [50 * (1/A2 + 1/A3 + 1/A4 + … + 1/A13)]

= 12 / (1/A2 + 1/A3 + 1/A4 + … + 1/A13)

Lets call this blue expression as (1).

Time for introducing the concept of Harmonic Mean.

The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.

Sounds confusing?!?

Take a sip of that coffee and read again.

reciprocal of the arithmetic mean of reciprocals

So harmonic mean of a range of numbers (say a,b,c,d…) is

=1/ [(1/a + 1/b + 1/c +…) / (count of numbers)]

or in other words,

= count of numbers / sum of reciprocals

Applying this concept to the range A2:A13, we get

= count of range / sum of reciprocals of A2:A13

= 12 / (1/A2 + 1/A3 + 1/A4 + … + 1/A13)

Now, isn’t the red expression of harmonic mean same as the blue expression (1) above?

Thus, to calculate the average speed, we just need harmonic mean of the the range A2:A13.

And there is a perfect formula for that.

=HARMEAN(A2:A13)

So, we can use that and it gives average speed for the trip in one step!

Special case – What if the speed is not tracked at equal distances?

Lets say Jack measured his speed at 40,50,60,40,60,50,40,50,60,60,50,40 mile intervals instead of every 50 miles.

In such case, we can’t use HARMEAN() because the distances are not equal. Fortunately, we can still use SUMPRODUCT.

Assuming the distance covered per interval is in the range B2:B13 (speeds are already in A2:A13),

The formula,

=SUM(B2:B13)/SUMPRODUCT(B2:B13/A2:A13)

tells us the average speed of the trip.

Learn more: Calculating weighted average using SUMPRODUCT.

Download the solution workbook

Click here to download the solution workbook. Examine the formulas to learn more.

As a bonus, It contains an additional problem to test your skills.

A twist in the tale – Tracking time instead of speed

Lets say after all this formula struggle, Jack (our driver of the road trip) wised up and started tracking time instead of speed. So his new log looks like this:

Now how do we calculate the average speed?

The time stamp data is in range A2:A16 and distance is in B2:B16.

Please post your formulas in the comments section.

PS: The solution workbook contains answer to this problem as well. Just unhide to see.

Go ahead and post your answers. This time, lets hope we get fewer than 1/3rd answers as wrong.

Learn more about formulas:

Check out any article from our formula forensics or Excel homework pages to learn something interesting & cool. Also go thru SUMPRODCUT & Advanced SUMPRODUCT articles to sharpen your formula writing skills.

Listen to our podcasts on averages to raise above your average.

Averages are mean – Part 1

Averages are mean – Part 2