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.
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.
187 Responses
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.
Harmean does indeed work. Now I have to find out what that is…
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.
By using Average formula we shall take out the answer. The answer is 67. =average(cell range)