2

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 results I get an entirely different result, 72.791. Also if I average each column & then average those results it gives a different number, 49.231. In trying to figure out why I created another sheet with the same number of columns & rows & filled it with random numbers. All the cells were filled with numbers & when I did the above all the results were the same. But when I started deleting values in some of the cells the 3 results began to differ. The more I deleted the bigger the difference became between the 3 averages. Can anyone explain why this happens & which result is correct? Is there a way I can upload a copy of my spreadsheet?

ZygD
  • 2,459
  • 12
  • 26
  • 43
James
  • 21
  • 1
  • If any answer helped to solve the problem please check the ✓ symbol next to the answer. – ZygD Apr 15 '21 at 07:32

3 Answers3

1

You are on the right track. I created a new table containing smaller numbers:
enter image description here

If you delete some numbers you can quickly see what is going on:
enter image description here

Now it is clearly visible that ALL averages are correct. E.g. column C only has number 3 in it. So the average is 3! Column 3 in total has over 1 million cells. Since most of them are empty, none of them are included into calculation. Excel never includes empty cells or cells with text in it into average calculation.

enter image description here

ZygD
  • 2,459
  • 12
  • 26
  • 43
0

Instead of deleting values, replace them with zeroes. If you do that, all of the averages remain the same. So Excel doesn't include empty cells in the average calculation. This means that a value in a row (or column) with a missing value will "influence" the average more than the values in other rows...and yet averaging those results weight each row the same. Hence the changing results.

Experiment with a much smaller test case to see even more profound changes.

user1040087
  • 1
  • Averages will not remain same if values are replaced with 0. Zero is not the same as empty cell. – ZygD Apr 12 '21 at 07:55
0

From an ExcelJet article:

The AVERAGE function automatically ignores text values and empty cells. However, zero (0) values are included. You can ignore zero (0) values and/or apply specific criteria using the AVERAGEIFS function.

The AVERAGE function will ignore logical values and numbers entered as text. If you need to include these values in the average, see the AVERAGEA function.

If the values given to AVERAGE contain errors, AVERAGE returns an error. You can use the AGGREGATE function to ignore errors.

ExcelEverything
  • 3,114
  • 1
  • 4
  • 16