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

Posted on August 8th, 2014 in Excel Challenges , Learn Excel - 185 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.

what-is-jacks-average-speed

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.

Click here to post your answer.

 

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.

Written by Chandoo
Tags: , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

185 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

  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)

    🙂

  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

  18. Adrian says:

    =HARMEAN(A2:A13)

    62.7094 MPH

    Your hint was very clear.

  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)}

  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" 🙂

  31. Pradeep D says:

    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)

  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)
    ANSWER 67

  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:

    ANSWER= 65.69

  42. Jagan says:

    Formula:- Average(A2:A13)
    Answer :- 67 Mph

  43. MF says:

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

  44. MF says:

    HARMEAN... cool!

  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
    the only correct answer is
    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.

  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. 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))

  59. 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.

  60. 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.

  61. 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?

    😉

  62. Stephen says:

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

  63. Bhavit says:

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

  64. Peter Thompson says:

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

  65. 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 🙂

  66. 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 !!

  67. Philippe J. says:

    =600/SUMPRODUCT(50/A2:A13)

  68. Seshasayee says:

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

  69. 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!

  70. karl says:

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

  71. 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

  72. Mehmet Gunal OLCER says:

    =HARMEAN(A2:A13)

    which is equal to 62.70939817

  73. Anwar says:

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

  74. Parandhaman says:

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

  75. Jolene says:

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

  76. Steve Hardy says:

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

  77. 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.

  78. Diego R says:

    =Harmean(A2:A13) = 62.7094

  79. 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)

  80. Jerry Giles says:

    Oops. The weighted average would be 50 mph.

  81. 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

  82. Akin says:

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

  83. JLT says:

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

  84. JLT says:

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

  85. Chandrashekara says:

    Answer: 67mph

    Use this formula =AVERAGE()

  86. 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.

  87. Kevin Frawley says:

    =+AVERAGE(A2,A13)

  88. deniz koçer says:

    =average(a2:a13)

  89. N.Sakthi Subramaian says:

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

  90. MIKE says:

    =AVERAGE(A2:A13) ANSWER 67

  91. 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

  92. Ram says:

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

  93. 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)}

  94. Lars says:

    avg(a2:a13)= 67 mph

  95. 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

  96. 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)

  97. Gangadharan says:

    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)

  98. Susan says:

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

  99. Bong says:

    62.2 miles per hour

  100. Rajender says:

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

    Lean a new thing from this website.

  101. 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

  102. Perumal Pillai says:

    =AVERAGE(A2:A4)

  103. 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

  104. Srinivas says:

    =AVERAGE(A2:A13)

  105. Sachin Bhor says:

    =AVERAGE(B2246:B2257)*1609.34

    1 mile = 1609.34 meter.

  106. Vivek Kr Pandey says:

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

  107. 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

  108. Amitha says:

    Formula: =Average(A2:A13)
    Answer : 67 Mph

  109. Candy says:

    =average(M2:M13)

  110. 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

  111. Kishan says:

    =Average(A2:A13) =67

    67MPH is the average speed which Jack travelled

  112. Lakshya says:

    Slavi Nechev's answer is correct.

  113. mehdibaret says:

    =average(A2:A13)

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

  114. 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

  115. 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.

  116. Maloo says:

    62.7094 mph
    =HARMEAN(A2:A13)

  117. 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.

  118. Marion Walker says:

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

  119. shahnawaz says:

    Answer is 67 mph average speed

  120. Pallab says:

    =AVERAGE(A2:A13)

    So, confusing

  121. 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.

  122. Praveen Netha.R says:

    =AVERAGE(A2:A13)

  123. Umair says:

    {=(600/SUM(50/(A2:A13)))}
    please note: matrix formula

  124. Jina says:

    =AVERAGE()

  125. Mitali says:

    =AVERAGE(A2:A13)

    =67

  126. Udayakumar says:

    Hi,
    My answers is
    =average(A1:A13)

  127. 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)

  128. Martin says:

    =average(a1:a12)

  129. John Peters says:

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

  130. Muthuvel says:

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

    Thank you

  131. 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))

  132. Gerald says:

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

  133. 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.

  134. Elie Dib says:

    62.70939817

  135. Tushara says:

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

  136. Jacinto says:

    =average(a2:a13)

  137. Mari A. says:

    Formula:

    =600/SUMPRODUCT(50/A2:A13)

  138. Sam says:

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

  139. Rita says:

    =Average(A2:A13)

    Answer is 67 Miles

  140. Rintu says:

    =average(A2:A13)
    67

  141. LaWRENCE Day says:

    67 mph

  142. Leah Williams says:

    =HARMEAN(A2:A13)

  143. Susan Brannan says:

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

  144. MikeO3 says:

    62.70939817

    =HARMEAN(D6:D17)

  145. ashoke basu says:

    +average(a1..a13)

  146. 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

  147. 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.

  148. 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.

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

  150. June says:

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

  151. sandeep says:

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

    =600/9.57=62.70

  152. 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?

  153. M says:

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

Leave a Reply