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.
8 Responses to “Create a Combination Chart, Add Secondary Axis in Excel [15 Second Tutorial]”
[...] Select the “daily completed” column and add it to the burn down chart. Once added, change the chart type for this series to bar chart (read how you can combine 2 different chart types in one) [...]
[...] set the height series to be plotted on secondary axis. Learn more about combining 2 chart types and adding secondary axis in [...]
[...] Excel Combination Charts – What are they? [...]
[...] To show the years, I have used another dummy series and plotted it on secondary axis (related: how to add secondary axis?) [...]
Thanks for this one!
[...] Choisissez la colonne « Daily Completed » et ajoutez-la au graphique. Une fois ajoutée, changez le type de graphique pour cette série à histogramme (lisez comment combiner 2 types de graphiques en un : combine 2 different chart types in one) [...]
How do i create a chart that has negative numbers on axis x and y and plot them correctly? I cannot seem to understand how to do this, please help.
Thanks.
Nat
You can also plot 2 or more Y axes in Excel using EZplot or Multy_Y from Office Expander.com
There is a demo version to try.
Cheers.