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

*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 speedand 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.

### Leave a Reply

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

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

The Average Speed to cover the whole 600 Miles for jack is 0.111667.

=AVERAGE(B2:B13)/600

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

=600/SUMPRODUCT(50/A2:A13)

...giving an answer of 62.7094 mph

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

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

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

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

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.

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

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.

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

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!

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)

🙂

Yes, I like this.

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.

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.

When averaging rates you use the Harmonic mean.

=HARMEAN(A2:A13)

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

62.7094 mph

=HARMEAN(A2:A13)

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)

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)

72,67163516

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

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

62.70939817

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

=HARMEAN(A2:A13)

62.7094 MPH

Your hint was very clear.

AMAZING...Never thought of this...

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

=62.7094

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

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

The average speed is 67 Miles/Hour

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

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

nice...

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

oops

ted

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

=HARMEAN(A2:A13)

62.7094 MPH

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.

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

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

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

67? Sumproduct of 50 & the respective speed

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)

=SUBTOTAL(1,A1:A12)

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

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)

52.2578318055422

=AVERAGE(A2:A13)

ANSWER 67

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.

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?

62.70939817 mph

Formula used = HARMEAN(A2:A13)

I used the Avg Formula = 67

ANSWER= 65.69

Formula:- Average(A2:A13)

Answer :- 67 Mph

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

HARMEAN... cool!

Totally agree 🙂

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

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

Ron

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!

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

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

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.

Sorry, a typo! it´s

{COUNT(A2:A13)*1/(SUM(1/A2:A13))} = 62.7093981666506

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

if non-uniform intervals:

=SUM(intervalRange)/SUM(intervalRange/AvSpdRange)

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

Also:

=COUNT(A2:A13)/SUMPRODUCT((1/A2:A13))

and

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

give the right result ~ 62.709

67 miles per hour

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

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

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.

=average(a2:a13)

62.70939817

Using the Harmean Function.

Averge function = 67.

=HARMEAN(A2:A13)

http://en.wikipedia.org/wiki/Mean#Harmonic_mean_.28HM.29

Regards,

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

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.

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.

@JM

You are correct

I think it was done as a method to introduce the Hamean() function

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?

😉

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

=AVERAGE(A2:A13)

problem1:

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

Formula

=Average(A2:A13)

Or

Sum(A2:A13)/12

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 🙂

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

=600/SUMPRODUCT(50/A2:A13)

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

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

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

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

=HARMEAN(A2:A13)

which is equal to 62.70939817

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

A2: =Harmean(A2:A13) = 62.7094

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

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

=AVERAGE(A2:A13)

which is equal to 67

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.

=Harmean(A2:A13) = 62.7094

67

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

{=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)

67

Oops. The weighted average would be 50 mph.

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

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

🙂

67

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

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

67

Answer: 67mph

Use this formula =AVERAGE()

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.

=+AVERAGE(A2,A13)

=average(a2:a13)

=average(A2:A13)

another formula is

=(Sum(a2:a13)/count(a2:a13))

=AVERAGE(A2:A13) ANSWER 67

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

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

Hi there, my answer is straight-forward and uses some basic physics. Here it is:

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

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

avg(a2:a13)= 67 mph

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

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)

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

62.2 miles per hour

miscalculation,

62.709398 mph is the answer..

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

Lean a new thing from this website.

Speed for every 50 miles

43

67

86

97

67

51

70

84

66

40

73

67.636

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

=AVERAGE(A2:A4)

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

=AVERAGE(A2:A13)

=AVERAGE(B2246:B2257)*1609.34

1 mile = 1609.34 meter.

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

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

Formula: =Average(A2:A13)

Answer : 67 Mph

=average(M2:M13)

=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

=Average(A2:A13) =67

67MPH is the average speed which Jack travelled

Slavi Nechev's answer is correct.

=average(A2:A13)

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

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

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.

62.7094 mph

=HARMEAN(A2:A13)

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.

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

Answer is 67 mph average speed

=AVERAGE(A2:A13)

So, confusing

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.

=AVERAGE(A2:A13)

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

please note: matrix formula

=AVERAGE()

=AVERAGE(A2:A13)

=67

Hi,

My answers is

=average(A1:A13)

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)

=average(a1:a12)

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

=Average(A2:A13)

Average sped = 67 kmpl

Thank you

I placed all speed values in column A.

They are in a1:a12

The formula is

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

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

Sorry! I meant 67. I had a typo.

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.

62.70939817

as the speeds are recorded at equidistant intervals, we can use :

=harmean(a2:a13)

=average(a2:a13)

Formula:

=600/SUMPRODUCT(50/A2:A13)

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

=Average(A2:A13)

Answer is 67 Miles

=average(A2:A13)

67

67 mph

=HARMEAN(A2:A13)

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

62.70939817

=HARMEAN(D6:D17)

+average(a1..a13)

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.

@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

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.

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

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.

62.70939817

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

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

=12*50==600

=50/43............................................................50/A13=9.57

=600/9.57=62.70

answer is 67

=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?

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