What is the average speed of this road trip? [homework]

Posted on August 8th, 2014 in Excel Challenges , Learn Excel - 186 comments

Its homework time again.

This time, lets tackle an interesting & everyday problem.

Lets introduce our protagonist of the story – Jack.

Jack likes long road trips, smell of freshly brewed Colombian coffee, clicky-clack sound of his computer keyboard. He hates toll plazas (they slow him down) & Potassium permanganate.

And oh yes, Jack is obsessive about analyzing every little bit of data in his life.

That brings us to the recent road trip he took.

It was a total of 600 miles.

And Jack tracked the speed at which he was covering every 50 mile distance.

The data is neatly typed in to an Excel workbook (snapshot below), in the range A2:A13.

The problem – what is Jack’s average speed?

At the end of the trip, Jack wants to know what his average speed is.

But his Excel skills are mediocre. So he approached you, an awesome analyst in the making.

Your mission, if you choose to accept, is this:

Figure out which formula calculates average speed and post it in the comments section

So what are you waiting for. Open up Excel, solve the problem and share your answers here. Make Jack happy.

Want a clue?

There is no harme.An unusual formula works too.

Want more challenges?

If you are hungry for helping more poor Jacks, check out our Excel Challenges & Home work pages. Test your Excel skills, solve something tricky and feel awesome.

 CP016: 3 Must have books for aspiring analysts Mapping relationships between people using interactive network chart
 Written by Chandoo Tags: homework, Learn Excel, Microsoft Excel Formulas Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

186 Responses to “What is the average speed of this road trip? [homework]”

1. Sanmaya says:

The Average Speed to cover the whole 600 Miles for jack is 0.111667.
=AVERAGE(B2:B13)/600

• silas says:

when using =AVERAGE you shouldn't divide by anything. The =average already does that for you.

2. Robert Clark says:

=600/SUMPRODUCT(50/A2:A13)

3. Robert Clark says:

...giving an answer of 62.7094 mph

4. Crisu says:

=600/SUM(50/A2:A13), as an array (with ctrl+shift+enter), giving 62,7093981666506 miles per hour.

5. Utkarsh says:

Why we are not using Average formula ?
i.e. =average(A2:A13)

Can anybody explain me? I really not understanding.... May be I am doing mistake somewhere..

• Haz says:

For example:
You travel 60 miles at 60mph, then you travel 60 miles at 30mph.
The average would give 45mph.

If you traveled 120 miles at 45mph, the time would be 2,66 hours.

But with simple math you know that you traveled 120 miles in 3 hours: 1 hour to cover 60 miles (60mph), then 2 hours to cover the next 60 miles (30mph).
The correct answer is 40mph: 120 / (60/60 + 60/30).

• Randy says:

Because it is really a weighted average. Some data points contribute more or less to the solution than others.

6. Time = distance/speed
Find the time needed for each 50 mile distance, given speed, using t= d/s
Sum the total time (it will give the total time needed to cover the journey of 600 miles)
Total distance is 600 miles (given)

Average speed = total distance/total time

Distance Speed Time
50 43 1.162790698
50 67 0.746268657
50 86 0.581395349
50 97 0.515463918
50 67 0.746268657
50 51 0.980392157
50 70 0.714285714
50 84 0.595238095
50 66 0.757575758
50 40 1.25
50 73 0.684931507
50 60 0.833333333

Total 600 67 9.567943842

Average 62.70939817 mph

What Robert and Crisu have done is combined the helper columns using a single array formula.

Vijay

• The 67 in the total line is the average speed if done as said by Utkarsh. It is clearly different from the answer we get. If distance was not mentioned, then Utkarsh's answer would be correct.

• Neil A says:

You cannot average a 'rate' without knowing that the parameters are comparable, else you will end up with tomato flavoured sardines in your tin of tuna...

7. Pintu Soni says:

The average speed is 64.23982869, 64.24(round off to two dec points).

total distance travelled is 600, and total time taken is 9 hours 34 minutes.

8. Justas says:

Array formula can be used:
={SUM(50*\$A\$2:\$A\$13)/(50*COUNT(A2:A13))}

Total_time/Total_distance
Total_time = sum (50*Speed(t))
Total_distance=50*times

9. Shaun says:

At first I was with Utkarsh on this, but I suspected that this was a trick question and was missing something. I followed Vijay's statement, and I think I get it.

If we know the mileage for each checkpoint and they are all consistent miles, then one might assume an average would be perfect, giving us 67. =AVERAGE(A2:A13)

To test this, I even added a 50 value in each cell under column B to do a weighted average and received the same value: 67. =SUMPRODUCT(A2:A13,B2:B13)/600 [Where each value in B2:B13 is 50]

But as Vijay pointed out, the question was about average speed, not MPH per 50 miles. I know it sounds the same, but weighting should be on the time, not the distance. (Miles per hour, not MPH per mile)

So, in my opinion, Robert's calculation, =600/SUMPRODUCT(50/A2:A13) , is the easiest to get to the answer of 62.7094 MPH.

I learned something today!!! 🙂 Thanks all!

10. Patrick M says:

If you are given a series of speeds over equal intervals of distance, then the average speed for the entire trip is the harmonic mean of the speeds. (Average, or arithmetic mean, will not give the right answer.)

Robert and Crisu used formulas which are generating a harmonic mean, and they got the right answers. However, Excel has a built in function to get the harmonic mean. So, being lazy by nature, I just used:

=HARMEAN(A1:A13)

🙂

• Pallab says:

Yes, I like this.

11. Patrick M says:

Note that if, instead of stating the speeds for intervals of equal length, we were given speeds for equal intervals of time, then the simple average, or arithmetic mean, would have been sufficient.

• Patrick M says:

Restating my last post:

Note that if, instead of stating the speeds for intervals of equal distance, we were given speeds for equal intervals of time, then the simple average, or arithmetic mean, of the speeds would have been sufficient.

12. Jeromy says:

When averaging rates you use the Harmonic mean.

=HARMEAN(A2:A13)

• XOR LX says:

Wonderful! And this is why the same answer can be achieved by the (longer) versions above, since, mathematically:

600/((50/A2)+(50/A3)+...+(50/A13))

=600/(50*((1/A2)+(1/A3)+...+(1/A13)))
=12/((1/A2)+(1/A3)+...+(1/A13))
=1/(((1/A2)+(1/A3)+...+(1/A13))/12)

which is precisely the definition of the harmonic mean, i.e. "the reciprocal of the arithmetic mean of reciprocals."

Regards

13. Krishna Teja says:

62.7094 mph
=HARMEAN(A2:A13)

14. Angelo says:

Hi, what formula would you use to calculate average speed if Jack tracked his speed at every 50,60,70,30, etc miles. If the miles are non-contiguous

• {=SUM(A2:A13)/SUM(A2:A13/C2:C13)} array formula
or =SUM(A2:A13)/SUMPRODUCT(A2:A13,1/(C2:C13)) non-array formula

both properly calculate with non-equal distances where
distances are in A2:A13
speeds are in C2:C13

Both calculate
(sum of segment distance)
------------------------------------------ (divided by)
(sum of segment time)

• Haz says:

In A2:A13 you have the miles: 50, 60, 70, 30, etc.
In B2:B13 you have the speed

Then the average speed of the total distance is
=SUM(A2:A13)/SUMPRODUCT(A2:A13/B2:B13)

15. okyay says:

72,67163516

16. okyay says:

Speed for every 50 miles 50 60
43 69,76744186 50 miles take 69,7674418604651 minutes =(\$C\$1*\$B\$1)/A2
67 44,7761194 50 miles take 44,7761194029851 minutes =(\$C\$1*\$B\$1)/A3
36 83,33333333 50 miles take 83,3333333333333 minutes =(\$C\$1*\$B\$1)/A4
97 30,92783505 50 miles take 30,9278350515464 minutes =(\$C\$1*\$B\$1)/A5
67 44,7761194 50 miles take 44,7761194029851 minutes =(\$C\$1*\$B\$1)/A6
51 58,82352941 50 miles take 58,8235294117647 minutes =(\$C\$1*\$B\$1)/A7
70 42,85714286 50 miles take 42,8571428571429 minutes =(\$C\$1*\$B\$1)/A8
84 35,71428571 50 miles take 35,7142857142857 minutes =(\$C\$1*\$B\$1)/A9
66 45,45454545 50 miles take 45,4545454545455 minutes =(\$C\$1*\$B\$1)/A10
40 75 50 miles take 75 minutes =(\$C\$1*\$B\$1)/A11
73 41,09589041 50 miles take 41,0958904109589 minutes =(\$C\$1*\$B\$1)/A12
60 50 50 miles take 50 minutes =(\$C\$1*\$B\$1)/A13
622,5262429 600 miles take 622,526242900013 minutes =TOPLA(C2:C13)

averege speed 72,67163516 =+(C1*TOPLA(A2:A13))/C14

• okyay says:

Speed for every 50 miles
43 50 1,162790698 =+B2/A2 miles take 1,16279069767442 hours
67 50 0,746268657 =+B3/A3 miles take 0,746268656716418 hours
86 50 0,581395349 =+B4/A4 miles take 0,581395348837209 hours
97 50 0,515463918 =+B5/A5 miles take 0,515463917525773 hours
67 50 0,746268657 =+B6/A6 miles take 0,746268656716418 hours
51 50 0,980392157 =+B7/A7 miles take 0,980392156862745 hours
70 50 0,714285714 =+B8/A8 miles take 0,714285714285714 hours
84 50 0,595238095 =+B9/A9 miles take 0,595238095238095 hours
66 50 0,757575758 =+B10/A10 miles take 0,757575757575758 hours
40 50 1,25 =+B11/A11 miles take 1,25 hours
73 50 0,684931507 =+B12/A12 miles take 0,684931506849315 hours
60 50 0,833333333 =+B13/A13 miles take 0,833333333333333 hours
600 9,567943842 =TOPLA(C2:C13) 600 miles take 9,5679438416152 hours

averege speed 62,70940 =B14/C14

17. 62.70939817
{=COUNT(A:A)*50/SUM(50/A2:A13)} - Array Formulae

=HARMEAN(A2:A13)

62.7094 MPH

• Jaydeep says:

AMAZING...Never thought of this...

19. ET says:

=COUNT(A2:A13)/SUMPRODUCT(--ISNUMBER(A2:A13),1/A2:A13)
=62.7094

20. Dan says:

=HARMEAN(\$A\$2:\$A\$13)

21. Ted Shwartz says:

two parts to solution
a) translate mph to drive 50 mile intervals to hours)
b) divide 600 miles by answer from part a to get mph

use array formula to do both in 1 neat step
={600/sum(60/a2:a13)}

22. Sathya Moorthy Rajam says:

The average speed is 67 Miles/Hour

23. K M Zachariah says:

{=600/sum(50/A2:A13)}

24. Steve LeLaurin says:

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

• Ted Shwartz says:

nice...

i had a typo in my formula and should have used 50 instead of 60
oops

ted

25. Steve LeLaurin says:

the array formula noted above calculates sum of the times elapsed for each leg: 50 miles / X mph = hours required for each leg. Dividing that time into total distance of 600 miles gives average rate: 62.7094

26. Rick says:

=HARMEAN(A2:A13)

62.7094 MPH

27. Bruno Yanez says:

Jack's has to calculate de weighted average: =sumsq(A2:A13)/sum(A2:A13) = 70.99 mph

and the average(A2:A13) = 67 mph to compare both speed.

In Addition he has to calculate deviations STDEVP(A2:A13) to review the maximus and minimus speed for every 50 miles.

28. Jaydeep says:

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

29. Jaydeep says:

=HARMEAN(A2:A13) just read of this formula today... Harmonic mean since this is a data set of positive numbers... @ equal intervals of distance . Thanks again Adrian...

30. Keshav says:

=HARMEAN(A2:A13)... Very clear clue.... "harme.An" 🙂

67? Sumproduct of 50 & the respective speed

32. Haz says:

Just as some of the replies above, you have to calculate the harmonic mean.
You can calculate it as COUNT(RANGE)/SUMPRODUCT(1/RANGE).

Excel has a formula that easily calculates it for you:
=HARMEAN(A2:A13)

33. Dominic says:

=SUBTOTAL(1,A1:A12)

• Dominic says:

Disregard...seeing some of the above responses, I see the error in my approach.

34. Janice Hoffman says:

67 miles per hours is the average speed
I calculated this by multiplying each 50 mile leg of the trip by the speed
Then I added all of those results (40,200) and divided it by the total trip miles (600)

35. john says:

52.2578318055422

36. Mehool Shah says:

=AVERAGE(A2:A13)

37. The Stig says:

I'm using Excel 2011 for Mac it case it matters for my question below.

I understand the basic concept of the formula. I use similar math in spreadsheets that I use to track lap times and average speed. I created a table called Speed_Table with speed in Column A and Miles in Column B.

I came up with two formula options.
{=Speed_Table[[#Totals],[Miles]]/SUM(50/Speed_Table[Speed for Every 50 Miles])}

and

=Speed_Table[[#Totals],[Miles]]/SUMPRODUCT(50/Speed_Table[Speed for Every 50 Miles])

Is it true to say the first version must be an array because it contains SUM and the second version does not need to be an array because is contains SUMPRODUCT?

I get the exact same answer, 62.70 both ways. However the first version returns an error unless I make it an array.

38. John Harkins says:

600 miles in 12 hours? Should be 50 mph on average. I just divided the 600 by the number of hours. Is it more complicated than that?

39. Ravi Dawar says:

62.70939817 mph

Formula used = HARMEAN(A2:A13)

40. Ned says:

I used the Avg Formula = 67

41. RAJESH says:

42. Jagan says:

Formula:- Average(A2:A13)

43. MF says:

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

44. MF says:

HARMEAN... cool!

• Wainers says:

Totally agree 🙂

45. rajinikanth says:

{=(COUNTA(A2:A13)*50)/SUM(50/(A2:A13))}

46. Ronald says:

If all of the segments are equal distance, can't you just average the range?

Ron

47. Stef@n says:

Hi
62,7094 mph
:)))

explanation:
to drive a distance of 100 mph, then the same distance at 60 mph results in an average speed of 75 mph and NOT of 80 mph!

48. Gernard Pannacci says:

={600/(SUM(1/A3:A14)*50)} = 62.7093981666506

49. Gernardo Pannacci says:

Same as ={COUNT(A1:A12)*1/(SUM(1/A1:A12))} = 62.7093981666506

50. john says:

I gave answer as 52.2578318055422. My working was correct BUT I used a distance of 500 miles. LESSON: read the question more carefully! Also I now know about HARMEAN and I refreshed on use of ARRAYs.

51. Gernardo Pannacci says:

Sorry, a typo! it´s
{COUNT(A2:A13)*1/(SUM(1/A2:A13))} = 62.7093981666506

52. MikeD says:

=COUNT(AvSpdRange)/SUM(1/AvSpdRange) Array-entered

if non-uniform intervals:
=SUM(intervalRange)/SUM(intervalRange/AvSpdRange)

53. Niefer says:

#1. =600/SUMPRODUCT((50/A2:A13))
pros: no array

#2. {=600/SUM(50/A2:A13)}
cons: array

#3. =HARMEAN(A2:A13)
pros: shortest
cons: known to 0.001% of Excel users.

• Niefer says:

Also:

=COUNT(A2:A13)/SUMPRODUCT((1/A2:A13))
and
{=COUNT(A2:A13)/SUM(1/A2:A13)}

give the right result ~ 62.709

54. Fedrick William says:

67 miles per hour

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

where B2:B13 is an array of the constant number 50.

55. andre' van Wyk says:

Formula : =SUM(B2:B13)/12 : The answer is : 67

The average speed is the sum of all the recorded speeds devided by the amount of recorded speeds.

56. Tarak says:

=average(a2:a13)

57. Bill Collins says:

62.70939817

Using the Harmean Function.

Averge function = 67.

58. Diego says:

=HARMEAN(A2:A13)
http://en.wikipedia.org/wiki/Mean#Harmonic_mean_.28HM.29

Regards,

59. Jason Stripinis says:

Can use either a conversion to time and back to rate using
=600/SUMPRODUCT((50/A2:A13))

or the harmonic mean using
=HARMEAN(A2:A13)

I prefer the first as the harmonic mean assumes uniform weighting.
Putting the bucket sizes in B2 to B13 allows this generic use:
=SUM(B2:B13)/SUMPRODUCT((B2:B13/A2:A13))

60. Roan says:

To help understand the need for Harmonic Mean, work an example backwards:
100 kms in 1 hour = 100 kph
100 kms in 30 mins = 200 kph
Arithmetic mean would give average speed of 150 kph. Since you travelled for 1.5 hrs, if you multipy travel time by arithmetic average speed, you get 225 kms travelled (1.5*150).
But you know you only travelled 200 kms, hence the need for Harmonic Mean, which is 200 kms / 1.5 hours = 133.33 kph.

61. JM says:

Befuddled as to why, in this example, Jack would use harmonic mean to figure out the average speed for his entire trip.

In order to calculate his average speed every 50 miles, he was obviously was recording his time every 50 miles.

But why not just divide total distance (600 miles) by total time? Total time = sum of times OR simply end time minus start time.

• Hui... says:

@JM
You are correct
I think it was done as a method to introduce the Hamean() function

62. Stephen says:

Now... if stages 4,5,8,9 and 12 were in Canada and the rest in the US (so he won't get a ticket), what would his average speed be?

Or, if we went in straight cardinal directions, alternating N, S, E, W with each stage, what was his average velocity?

😉

63. Stephen says:

Duh - there's 12 stages... his average velocity would be 0.

• John Liddell says:

=AVERAGE(A2:A13)

64. Bhavit says:

problem1:
=(COUNT(E7:E18)*50)/SUMPRODUCT(50/E7:E18)

65. Peter Thompson says:

Formula
=Average(A2:A13)
Or
Sum(A2:A13)/12

66. Robert says:

Agree with the original
=600/SUMPRODUCT(50/B2:B13)
but, to make it more dynamic, you could use
=COUNTA(B2:B13)*50/SUMPRODUCT(50/B2:B13)
to make it less dynamic
=62.7094

in the future, tell Jack to take time stamps of his journey and not the beginning and end mileage on his car. A little bit easier to calculate

outside of Chandoo's cool homeworks, keep in mind real world uses:
- make the formula as dynamic as possible if you know you're going to re-use or have other variables (changing the measurement intervals)
- sometimes it's good to have multiple columns so someone can follow the formulas if you, the Excel expert, are not at work 🙂

67. Anderson Coral says:

Hi everybody !!

The formula to solve the problem is: {=600/SOMA(50/A2:A13)}
Ou, for a more complicated method:

speed for every 50 miles
43 1,162790698
67 0,746268657
86 0,581395349
97 0,515463918
67 0,746268657
51 0,980392157
70 0,714285714
84 0,595238095
66 0,757575758
40 1,25
73 0,684931507
60 0,833333333
time 9,567943842
time 62,70939817

Hello, from Brazil !!

68. Philippe J. says:

=600/SUMPRODUCT(50/A2:A13)

69. Seshasayee says:

=600/SUM(50/B2:B13) with ctrl+shift+enter

70. Mary says:

What! Not ready for the test, send info on how to find the damn average. Thanks Chandoo and Happy Independent India to you!

71. karl says:

=600/(50*sumproduct(1/a2:a13))

72. Randy says:

Leg Avg Spd Miles Driven Time on Road
1 43 50 1.162790698
2 67 50 0.746268657
3 86 50 0.581395349
4 97 50 0.515463918
5 67 50 0.746268657
6 51 50 0.980392157
7 70 50 0.714285714
8 84 50 0.595238095
9 66 50 0.757575758
10 40 50 1.25
11 73 50 0.684931507
12 60 50 0.833333333
Trip Total 62.70939817 600 9.567943842

73. Mehmet Gunal OLCER says:

=HARMEAN(A2:A13)

which is equal to 62.70939817

74. Anwar says:

A1: =Average(A2:A13) = 67 or
A2: =Harmean(A2:A13) = 62.7094

75. Parandhaman says:

Need to place 50 in next to distance, and do a sumproduct(the 50,speed)/sum of (50)

76. Jolene says:

The average speed was 67 MPH. The formula is =AVERAGE(A2:A13)

77. Steve Hardy says:

=AVERAGE(A2:A13)
which is equal to 67

78. roger van decraen says:

the average speed is 62,7094

I used the formula {600/sumproduct(60/(a2:a13))} assuming that the data is in the range A2:A13 and you can use only one formula.

79. Diego R says:

=Harmean(A2:A13) = 62.7094

80. Jeff says:

67

• Jeff says:

I understand the clue in the post now. I agree with Mehmet.

81. Simon says:

{=50*COUNT(A2:A13)/SUM(50/A2:A13)} or =Harmean(A2:A13)

or by making the speeds in Column A a table called tblSpeed we can use this formula which will dynamically resize when we add additional 50 mile speeds to the bottom as long as we put the formula in say cell C1

=Harmean(tblSpeed)

82. Jerry Giles says:

Oops. The weighted average would be 50 mph.

83. Akin says:

speed distance time hrs
43 50 =B2/A2
67 50 =B3/A3
86 50 =B4/A4
97 50 =B5/A5
67 50 =B6/A6
51 50 =B7/A7
70 50 =B8/A8
84 50 =B9/A9
66 50 =B10/A10
40 50 =B11/A11
73 50 =B12/A12
60 50 =B13/A13
=SUM(B2:B13) =SUM(C2:C13)
=B14/C14
62.7093981666506 mph

84. Akin says:

Next question is how many speeding tickets did Jack get during this road trip?

85. Widya says:

67

86. JLT says:

{=COUNT(B2:B13)/SUM(A2:A13/B2:B13)*50}

87. JLT says:

Calculating an average speed of 62.70939817 MPH. I entered my data in column B instead of A, hence the "B" references.

88. jamel says:

67

89. Chandrashekara says:

Use this formula =AVERAGE()

90. Ted says:

When dealing with rates or ratios, it is better to deal with the units and not let the quantities confuse you.
The average speed will need the units miles/hour, that is, the total miles and the total hours.
The terms given currently have the units miles/hour and miles for each segment. If we perform any acceptable math with the two terms we have (we can't add or subtract them, they have dissimilar denominators), we either lose or square one of the units, namely miles. That won't work.
We can add miles to miles and hours to hours or even miles/hour to miles/hour to get us the total miles and the total hours. We need another term that will give us another unit for the math to work, that unit is "hour".
As I stated before, if we divide what units we have, we can eliminate the miles unit and be left with hours (miles/miles/hour = hour). We can determine what time it took to travel 50 miles at the rate given for each segment. Now we have the hours for each segment and the miles for each segment.
So, now we can determine what the total miles traveled are (that was given) and divide it by the total time it took to drive that amount and dividing total miles by total hours gives us our average speed. Voila!
If we use helper cells and put 50 in each cell of B2:B13 and in cells C2:C13 we divided B by A, it might make things easier to see. In B14 we put the sum of the miles traveled in each segment (600) and in cell C14 we put the sum of the time in each segment (9.567944), and then in another cell we divide B14 by C14, we get our answer (62.7094). In Excel-speak we would boil that down to: =SUM(B2:B13)/SUMPRODUCT(B2:B13/A2:A13) or simplify it to =600/SUMPRODUCT(50/A2:A13) since 600 was given in the original problem.
Using the harmonic mean function works ONLY when the distance traveled in each segment or the amount of time in each segment is identical. Otherwise, you would have weighted segments and use the SUMPRODUCT method.

91. Kevin Frawley says:

=+AVERAGE(A2,A13)

92. deniz koçer says:

=average(a2:a13)

93. N.Sakthi Subramaian says:

=average(A2:A13)
another formula is
=(Sum(a2:a13)/count(a2:a13))

94. MIKE says:

95. Ratesh Sud says:

The correct answer in my opinion is 62.7094. I did not see the above answers before I worked it out in excel using helper column in B and C (like Vijay Raghavendran has done)

COLUMN A Speed for every 50 miles

COLUMN B Miles Per Segment

COLUMN C Time taken for every 50 miles

DATA

43 50 69.7674
67 50 44.7761
86 50 34.8837
97 50 30.9278
67 50 44.7761
51 50 58.8235
70 50 42.8571
84 50 35.7143
66 50 45.4545
40 50 75.0000
73 50 41.0959
60 50 50.0000

Averages B2:B13 = 50.0000
Averages C2:C13 = 47.8397

=AVERAGE(B2:B13)*60/AVERAGE(C2:C13)

The answer works out to 62.7094

I agree with Niefer that the array method is a complicated approach to many users (including me) and HARMEAN function was unknown to me till date.

I agree with Ted that Using the harmonic mean function works ONLY when the distance traveled in each segment or the amount of time in each segment is identical

96. Ram says:

"=Average(range) will give you the average speed of Jack

97. Danail says:

Hi there, my answer is straight-forward and uses some basic physics. Here it is:
{=COUNT(A2:A13)*50/SUM(50/A2:A13)}

• Danail says:

Oh, I forgot to post the result: the average speed is 62.71 (rounded up from 62.70939817)

98. Lars says:

avg(a2:a13)= 67 mph

99. Rohit says:

1 43 0.86
2 67 1.34
3 8 0.16
4 86 1.72
5 97 1.94
6 67 1.34
7 51 1.02
8 70 1.4
9 84 1.68
10 66 1.32
11 40 0.8
12 73 1.46
13 60 1.2
Total time--> 16.24
Avg speed--> 36.94581281

100. kefkolo says:

I'm pretty new at Excel so this is probably a lot trickier than I'm seeing it. I just listing all the values (A1:A12) and used the average formula.
=Average(A1:A12) and came up with 67. To check myself (or rather the formula) I also did =Sum(A1:A12)/12 and got the same answer.

However, one problem I saw was that you said the total miles was 600. The total of the miles "Jack" recorded was 804.

Thanks,
Kirk (aka: Kefkolo)

Generally speed is defined as distance traveled/time conceived. In this case we do not have time.

In this case i believe it should be =Average(A2:A14)

102. Susan says:

=AVERAGE(F17:F29) =68.46153846 or 68 MPH

103. Bong says:

62.2 miles per hour

• Bong says:

miscalculation,

104. Rajender says:

=600/SUMPRODUCT(50/A2:A13) is easiest one for me.

Lean a new thing from this website.

• Kofi says:

Speed for every 50 miles
43
67
86
97
67
51
70
84
66
40
73
67.636

105. Slavi Nechev says:

B2=1/A2
B3=1/A3
B4=1/A4
B5=1/A5
B6=1/A6
B7=1/A7
B8=1/A8
B9=1/A9
B10=1/A10
B11=1/A11
B12=1/A12
B13=1/A13
B14= SUM(B2:B12)
Average velocity B15 = 12/B14

106. Perumal Pillai says:

=AVERAGE(A2:A4)

107. Johan Schosinsky says:

First of all... which average speed is needed? The "on-standard" speed or the "off-standard" speed? All formulas above would be "somewhat" good if we assume Jack did NOT make any stop to drink his beloved brewed Colombian coffee... or any stop to eat something... I doubt he did not do it in 600 miles!! So, we would not be able to calculate his real average speed on road unless we were given that data... How much stops and how many time on each stop ?

Then, assumming he did NOT STOP at all... above calculations are still not very accurate as he is changing speeds from one interval to another... so he is NOT DRIVING at a constant speed, thus having acceleration and de-acceleation on some intervals!! The formulas for calculating time using acceleration are different ! Initial speed = 0, final speed on first block is 43, distance is 50, so acceleration is 18.49 for this interval... and time is 2.32 for this interval.

Doing this for all intervals and adding all times we get total elapsed time of 10.61 and total distance of 600 miles, thus average speed ( assuming NO stops ! ) is 56.52579...

Now help me experts in Excel...!! Give me the array formula on just one cell without helper columns for this! Formulas below.

Final speed ^2 - Initial speed ^2 = 2 * acc * dist
acc * time = Final speed - Initial speed

108. Srinivas says:

=AVERAGE(A2:A13)

109. Sachin Bhor says:

=AVERAGE(B2246:B2257)*1609.34

1 mile = 1609.34 meter.

110. Vivek Kr Pandey says:

I think 67 mph. ... m i right ..? o_O

111. Hari Singh says:

Sr No KM Speed Total Time Taken
1 50 43 1.16
2 50 67 0.75
3 50 86 0.58
4 50 97 0.52
5 50 67 0.75
6 50 51 0.98
7 50 70 0.71
8 50 84 0.60
9 50 66 0.76
10 50 40 1.25
11 50 73 0.68
12 50 60 0.83

Total 600 804 9.57

Average Speed 62.71

112. Amitha says:

Formula: =Average(A2:A13)

113. Candy says:

=average(M2:M13)

114. Candy says:

=average(A2:A:13)
=67
There was no time given & distance per trip (50miles) is constant therefore the total of 600 miles is irrelevant... I supposed 67 is the average speed for the 12 trips.

However assuming that the speed is at per hour basis, then the formula must be: =600/sumproduct(50/A2:A:13) = 62.709

115. Kishan says:

=Average(A2:A13) =67

67MPH is the average speed which Jack travelled

116. Lakshya says:

117. mehdibaret says:

=average(A2:A13)

I really not understanding…. May be I am doing mistake somewhere.

118. Sonu Kumar says:

Jack took 804 Min or 804/60 = 13.40 Hrs. to travel 600 Miles. Average Speed would be 600/13.40 = 44.78 Mph.

Minutes Miles
43 50
67 50
86 50
97 50
67 50
51 50
70 50
84 50
66 50
40 50
73 50
60 50
13.40 600
=SUM(A2:A13)/60 =SUM(B2:B13) =B14/A14

119. Bokhari says:

The answer is 67 mph. We find the result by formula :
=average(a2:a13) because all range are equal to 50 miles.
we can also use :
somprod(a2:a13;b2:b13)/600.

120. Maloo says:

62.7094 mph
=HARMEAN(A2:A13)

121. LALIT says:

answer is 62.70939817 mph (average speed)
method is we have to convert speed into time taken for each 50 miles the add total time and divide it by total distance and you will get your answer.

122. Marion Walker says:

I'd just highlight all the speeds and look at the bottom of the sheet to view the average

123. shahnawaz says:

Answer is 67 mph average speed

124. Pallab says:

=AVERAGE(A2:A13)

So, confusing

125. Pallab says:

Finally, may be:

{=600/SUM(B2:B13/A2:A13)}

Here, every cell contains 50 from B2 to B13. The result is 62.7094

The {} sign comes when you press Ctrl+Shift+Enter after putting the formula in a cell.

126. Praveen Netha.R says:

=AVERAGE(A2:A13)

127. Umair says:

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

128. Jina says:

=AVERAGE()

129. Mitali says:

=AVERAGE(A2:A13)

=67

130. Udayakumar says:

Hi,
=average(A1:A13)

131. Amit Singh says:

43 =\$A\$16/A2 1.16279069767442
67 =\$A\$16/A3 0.746268656716418
86 =\$A\$16/A4 0.581395348837209
97 =\$A\$16/A5 0.515463917525773
67 =\$A\$16/A6 0.746268656716418
51 =\$A\$16/A7 0.980392156862745
70 =\$A\$16/A8 0.714285714285714
84 =\$A\$16/A9 0.595238095238095
66 =\$A\$16/A10 0.757575757575758
40 =\$A\$16/A11 1.25
73 =\$A\$16/A12 0.684931506849315
60 =\$A\$16/A13 0.833333333333333
=SUM(B2:B13) 9.5679438416152
Distance cover an each interval
50
Total Distance cover 600
Total time taken 9.5679438416152
Average speed =B17/B18 =62.7093981666506 (62.71 mph)

132. Martin says:

=average(a1:a12)

133. John Peters says:

{=COUNT(A2:A13)*50/SUM(50/A2:A13)} CSE

134. Muthuvel says:

=Average(A2:A13)
Average sped = 67 kmpl

Thank you

135. Martin Lau says:

I placed all speed values in column A.
They are in a1:a12

The formula is

= 600 / Sumproduct ( 50* 1/ ( a1:a12))

136. Gerald says:

The average formula is what I used in excel. The answer is 84 mph.

• Gerald says:

Sorry! I meant 67. I had a typo.

137. Manuel Mtz says:

You can use Average (A1:A14) which gives you an average of 65.69 MPH.

If you do it by a ratio (Speed/50) for each measurement, the ratio is 1.31, multiply it by 50 and it gives you 65.69 MPH as well.

I believe that in this case, simple is better, if he was doing a road test for a car company, statistics will be very helpful to get the whole deal.

138. Elie Dib says:

62.70939817

139. Tushara says:

as the speeds are recorded at equidistant intervals, we can use :
=harmean(a2:a13)

140. Jacinto says:

=average(a2:a13)

141. Mari A. says:

Formula:

=600/SUMPRODUCT(50/A2:A13)

142. Sam says:

=600/sum(50/(A2:A13)) (ctrl+shift+enter)

143. Rita says:

=Average(A2:A13)

144. Rintu says:

=average(A2:A13)
67

145. LaWRENCE Day says:

67 mph

146. Leah Williams says:

=HARMEAN(A2:A13)

147. Susan Brannan says:

The answer is 67, use autoformula drop down menu to access 'average', from the editing group on the home tab.

148. MikeO3 says:

62.70939817

=HARMEAN(D6:D17)

149. ashoke basu says:

+average(a1..a13)

150. Brendan says:

If you travelled 600 miles at an average speed of 62.7mph your travel time would be 9.57 hours which is 574 minutes.

The sum of the minutes recorded in the list is 804 minutes so he must be going substantially slower than 62.7mph.

I think the answer is 44.8mph based on the formula =600/(SUM(A2:A13)/60).

As a sense check if you were doing 60mph you would take 50 minutes to travel 50 miles and 9 of the 12 data points are above 50 minutes so the speed must be below 60 mph.

• Hui... says:

@Brendan
Column A is speed not time
I am assuming Speed is in MPH (Miles per Hr)
All segments are 50 Miles in Length
So segment 1 Speed is 43 mph
v=d/t so
t=d/v
=50/43
=1.16 Hrs
=69.77 Mins

If he was doing 60 mph it would take 50 mins to do 50 Miles
As his speed was less than 60 mph it will take more than 50 mins to do the 50 miles

tabulating all results
speed Dist Miles Time Hrs Time Mins
43 50 1.16 69.77
67 50 0.75 44.78
86 50 0.58 34.88
97 50 0.52 30.93
67 50 0.75 44.78
51 50 0.98 58.82
70 50 0.71 42.86
84 50 0.60 35.71
66 50 0.76 45.45
40 50 1.25 75.00
73 50 0.68 41.10
60 50 0.83 50.00
600 9.57 574.08

Shows that it does take 574 Mins = 9.57 Hrs at an average speed of 62.71 mph

151. Johan Schosinsky says:

Brendan - You are right that it should be less than 60 mph !

If you check my previous post, I ended up with 56.52 mph and with 10.61 elapsed hours. And this is explained by the fact that Jack did not travel at a constant speed in every "block", as all the previous calculations assume. There are speed "blocks" that show acceleration, as he finished those at a higher speed than when he started, or de-acceleration, finishing at lower speed than when he started. When there is acceleration, the formula distance = speed * time is not longer valid, so you need to use acceleration formulas to calculate elapsed time on each "block" and then use the harmonic mean, as all "blocks" show a constant distance.

• Hui... says:

@Johan
Velocity = Dist / Time
eg: Miles per Hr

so Distance = Velocity * Time
This is only applicable at a Constant velocity

The true formula is Distance = Sum (integrals of Velocity * Time) where time goes towards zero

meaning that you add up all the minute segments of constant velocity and the appropriate times at that velocity for minutely small time frames

Of course practically this is near impossible.
But the averages still add up

Meaning that the Average Velocity for a segment (Which is what Chandoo gave us) is equal to 50/time
so the time per segment is 50/velocity
The instantaneous or spot velocities and startup/slow down times don't come into this calculation as they are averaged out.

152. Johan Schosinsky says:

Hui - Thanks for clarification !

I truly understood that the speeds given by Chandoo on each block were the speeds at each 50 miles, meaning the "spot speeds" and not the "average speeds" of the block, as first block does not show any speed, and last one is #13 instead of #12.

And, yes, to be more accurate, as you mention, we could plot every "position" or distance ( y ) at every small time interval ( x ) until completion of 600 miles, then determine the function f(x) of these "positions" and calculate the derivative of it to obtain the overall speed.

153. 62.70939817
=(COUNT(\$A\$2:\$A\$13)*50/SUM(60/A2:A13*50))*60

154. June says:

I calculated the time for each 50 mile section. Totaled them. Divided that total into 600 miles for the average speed.

155. sandeep says:

=12*50==600
=50/43............................................................50/A13=9.57

=600/9.57=62.70

156. shriti says:

157. M says:

=600/SUM(50/A2,50/A3,50/A4,50/A5,50/A6,50/A7,50/A8,50/A9,50/A10,50/A11,50/A12,50/A13)

Is there a simpler way?

158. M says:

In a way glad I didn't read the comments. Doing it the longer way means the shorter formulas are more memorable.

159. Vijayalaxmi Pattar says:

By using Average formula we shall take out the answer. The answer is 67. =average(cell range)

 CP016: 3 Must have books for aspiring analysts Mapping relationships between people using interactive network chart