CODEWITHSUNDEEP
excelsumaverage
nylkoorb
nylkoorb

Reputation: 29

Excel: why is the average of divisions different from division of sums?

So lets say I have two variables x and y. I calculate the total sum of each variable.

I calculate y/x for each row and also calculate y/x for the sum of both x and y columns (280/10=28).

I would expect this value (28) to be equal to the average of y/x (230/4=32.5), but it is different.

This might have a simple explanation, but I can't seem to find it.

An example here

Thanks in advance

Upvotes: 2

Views: 5086

Answers (1)

trincot
trincot

Reputation: 350290

This is really a mathematics question. The two are not equal:

                        10    200    30    40
                        ─── + ──── + ─── + ─── 
10 + 200 + 30 + 40       1      2     3     4  
──────────────────  ≠  ────────────────────────
   1 + 2 + 3 + 4                   4

There really is no reason why they could be expected to be the same: when the second expression is rewritten with one common denominator, it becomes even more evident there is little it has in common with the first expression.

To get a common denominator, one finds the least common denominator, which in this case is 12, and so the second expression could be written as follows:

 120   1200    120    120  
──── + ──── + ──── + ──── 
 12     12     12     12 
──────────────────────────
            4

Which is simplified to:

120 + 1200 + 120 + 120   5 + 50 + 5 + 5   65
────────────────────── = ────────────── = ── = 32.5
          48                    2          2

There clearly is no relation with the first expression.

What is equal?

10 + 200 + 30 + 40   AVG(10, 200, 30, 40)
────────────────── = ────────────────────
   1 + 2 + 3 + 4       AVG(1, 2, 3, 4)

This works because you really divide both numerator and denominator with the same factor (4), which is a null-operation.

Upvotes: 4

Related Questions