Average issue or my lack of understanding math?

[jsteele]

[TABLE="width: 512"]
<tbody>[TR]
[TD][/TD]
[TD]hours[/TD]
[TD]complete[/TD]
[TD]avg hr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]85.5[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]2.39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0.68[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]175.25[/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]1.68[/TD]
[TD][/TD]
[TD="colspan: 5"]why are these averages different?[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]131.5[/TD]
[TD="align: right"]383[/TD]
[TD="align: right"]2.91[/TD]
[TD][/TD]
[TD="colspan: 5"]which one is the true average?[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]469[/TD]
[TD="align: right"]3.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]143.5[/TD]
[TD="align: right"]592[/TD]
[TD="align: right"]4.13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]total[/TD]
[TD="align: right"]793.75[/TD]
[TD="align: right"]2032[/TD]
[TD="align: right"]2.59[/TD]
[TD] or[/TD]
[TD="align: right"]2032[/TD]
[TD]dived by[/TD]
[TD="align: right"]794[/TD]
[TD]equals[/TD]
[TD="align: right"]2.56[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Cross-footing involves summing, not averaging.

So, run

=C2/B2

also on the total row. This means 2.56 closer to truth.

In other words, avoid averaging the average values.

 

[jsteele]

Yes, =C2/B2 is what I used for each row, so for row 1 the average per hour is 2.39

The 2.59 was the average of the avg per hour row. It seams what you are saying is from a totals perspective and to get the average total, I would continue to use the Total Complete / Total Hours. In this case it would be C8/C7 ?

I can''t seem to understand why averaging the averages doesn't come out the same. Maybe I need to research this "cross-footing"

 

[Aladin Akyurek]

The moral: Do not average average values.

Try to read up on floating point arithmetics.

 

[kvsrinivasamurthy]

2.59 is
(C2/B2+C3/B3+C4/B4+C5/B5+C6/B6+C7/B7)/6

2.56 Which is correct average is

(C2+C3+C4+C5+C6+C7)/(B2+B3+B4+B5+B6+B7)

Both the formulas are not equal. So second formula is the correct one.

 

[Fazza]

@jsteele, consider this smaller example

100 hrs, 1 complete, avg 0.01/hr
1 hr, 100 complete, avg 100/hr

TOTALS
101 hrs, 101 complete

averaging the averages would give 100.01/2 = 50.005/hr
using the totals, the average is 1 /hr

HTH

 

[Rick Rothstein]

I can''t seem to understand why averaging the averages doesn't come out the same.

Let's look at a simple example to see that they are not the same. Consider these four numbers...

1
3

100
300
500

The average of the first two numbers is 2 and the average of the second three numbers is 300... hence, the average of the averages is 151 (304/2). Now, if we add all the numbers up (904) and divide by the number of values (5), that average is 180.8. The average of the numbers and the average of the averages are not only not equal to each other, they are not even close to each other.

 

[joeu2004]

I can''t seem to understand why averaging the averages doesn't come out the same.

Well, sometimes it does. But we shouldn't count on it.

Do you understand algebra?

Consider the average of a, b, c, d, e and f. You know it is (a+b+c+d+e+f)/6.

Now suppose we have (a+b)/2 and (c+d+e+f)/4, two averages. Their average is:

( (a+b)/2 + (c+d+e+f)/4 ) / 2 = (a+b)/4 + (c+d+e+f)/8.

Obviously not the same as (a+b+c+d+e+f)/6 = (a+b)/6 + (c+d+e+f)/6

-----

When is the average of averages equal to the average of all?

When each sub-average has the same divisor. Consider:

( (a+b+c)/3 + (d+e+f)/3) ) / 2 = (a+b+c)/6 + (d+e+f)/6 = (a+b+c+d+e+f)/6

 

[jsteele]

Lots of support here. Thanks All, I think I am understanding why it isn't working with all your great examples now!