Different results from AVG funtion

[MaggieMagill]

I'm building a basic miles per gallon spreadsheet. One column is miles,
another gallons (fancy,huh).

On each row I calc the MPG by miles/gallons. Simple enough. At the bottom I
total all the miles entries and all the gallons entries. MPG is then
calculated by dividing the total miles by total gallons.

I can end up with two different results by

1 - using "AVERAGE" on all the MPG values in the column
2 - Dividing the total miles by total gallons

Column "B" is the miles.
Column "C" is the gallons.
Column "E" is the MPG. Calculated by SUM(Bn/Cn)
Rows run from 2 to 32.
Cell B34 contains the result of SUM(B2:B32).
Cell C34 contains the result of SUM(C2:C32)

1 - AVERAGE(E2:E32) provides 25.24
2 - SUM(B34/C34) and/or SUM(B2:B32/C2:C32) provides 25.13

But why is there such a difference between that and using AVERAGE on the
column of calculated averages as shown in RESULT #1?

In a shorter, 2 row version it calcs like such:

B C E
MILES GALS MPG AVG of MPGs
2 277.3 9.775 28.37 28.37
3 82.8 3.001 27.59 27.98 = AVERAGE(E2:E3)RESULT #1

 

[Ron Rosenfeld]

I'm building a basic miles per gallon spreadsheet. One column is miles,
another gallons (fancy,huh).

On each row I calc the MPG by miles/gallons. Simple enough. At the bottom I
total all the miles entries and all the gallons entries. MPG is then
calculated by dividing the total miles by total gallons.

I can end up with two different results by

1 - using "AVERAGE" on all the MPG values in the column
2 - Dividing the total miles by total gallons

Column "B" is the miles.
Column "C" is the gallons.
Column "E" is the MPG. Calculated by SUM(Bn/Cn)
Rows run from 2 to 32.
Cell B34 contains the result of SUM(B2:B32).
Cell C34 contains the result of SUM(C2:C32)

1 - AVERAGE(E2:E32) provides 25.24
2 - SUM(B34/C34) and/or SUM(B2:B32/C2:C32) provides 25.13

But why is there such a difference between that and using AVERAGE on the
column of calculated averages as shown in RESULT #1?

In a shorter, 2 row version it calcs like such:

B C E
MILES GALS MPG AVG of MPGs
2 277.3 9.775 28.37 28.37
3 82.8 3.001 27.59 27.98 = AVERAGE(E2:E3)RESULT #1

Apples and Oranges.

One result is the Average MPG per TRIP
The other result is your overall average MPG.


For example, if you had one trip of 500 miles and you used 20 gallons, your MPG
for that trip is 25 MPG.

On your next trip, you go one (1) mile and you use one (1) gallon. Your MPG
for that trip is 1 MPG.

Your average MPG per trip is 13 MPG.

But your average overall MPG is 23.86 MPG

Sorry for the long post but just what am I missing???
Am I going goofy from looking at numbers all nite?

--ron

 

[Gary's Student]

You are 100% correct and are missing nothing. The two methods will produce
completely different results.


The correct method is to take the total miles and divide by the total gallons.


Consider the following case:

trip1 is 3000 miles using 100 gallons
trip2 is 10 miles using 1 gallon of gas (creeping alone)
If you sum the miles and sum the gallons the average mileage will be about
29.8
If you incorrectly tried to average the mileages (30 mpg and 10 mpg) you
would (incorrectly) get 20 mpg.

 

[Not Me]

You are 100% correct and are missing nothing. The two methods will
produce completely different results.


The correct method is to take the total miles and divide by the total
gallons.


Consider the following case:

trip1 is 3000 miles using 100 gallons
trip2 is 10 miles using 1 gallon of gas (creeping alone)
If you sum the miles and sum the gallons the average mileage will be
about 29.8
If you incorrectly tried to average the mileages (30 mpg and 10 mpg)
you would (incorrectly) get 20 mpg.

Ahhh - ok. I see it now. I DID get number goofy. The total, overall MPG
being the average or actual MPG (total miles/total gals) (which made
perfect sense to me) and the other being the average of the averages of
each MPG. AVG(AVG(MPG)). I'm sure I'm not explaining this properly, but
thanks to you both using a wider range of numbers it became very obvious
what I failed to acknowledge.

MPG is an average and I averaged the averages expecting to "prove" the
actual average. Well, that's pretty average for me!

Thanks to you both! Now I can finally go to bed...