Forum Discussion

Copper Contributor

Apr 11, 2021

Excel 2007 average discrepency

I am averaging a group of numbers in Excel 2007 that have 21 columns & 42 rows. When I average all the cells at once I get one result, 59.25183, but when I average each row & then average those resul...

excel

Formulas and Functions

JMB17

Bronze Contributor

Apr 11, 2021

Bettypin

When all of the cells are filled in, then the individual row and column averages have the same denominator, so the average of parts is equivalent to the average of the whole.

Say you have a 3x3 grid of numbers (X1 through X9). Averaging the rows or columns and then averaging those results can be represented as:

If you remember your algebra, fractions with the same denominator can be added, so this is equivalent to:

which simplifies to this, and is the exact same as the average of the whole:

When you start deleting cells, the average function excludes those cells. Say you delete X2, X3, and X4. You then have this, where each average of the parts (fractions in the numerator) have different denominators:

Then, getting common denominators and simplifying, you will see that averaging the parts is not the same as averaging the whole:

Bettypin

Copper Contributor

Apr 13, 2021

If a person can remember how to do it, algebra is very useful. My trouble is that not only do I not remember most of how to do it, I can't even remember that there's a way to do it. If I could remember that much then I could always look it up. Maybe when Mark Twain said, "There's lies, **bleep** lies, and statistics" he was thinking about people like me who can sort of do it but not correctly. One example I saw on the internet showed that David Justice's batting average was better than Derek Jeter year over year but when done correctly overall Derek's was better. It showed me that the interpretation of statistics is not as easy as it looks.
Thanks for the reply