Share via

Why does average give a different result if reference cells are reversed?

Anonymous
2022-11-30T17:16:17+00:00

I'm comparing two average equations in a data set of 40 results and 36 results were averaged together. The equations =Average(A9:A40,A3:A6) and =Average(A3:A6,A9:A40) when compared using =C40=C41 come up with two different values. Why would the order of reference cells change the result of the formula?

Screenshot for reference.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2022-12-01T07:24:22+00:00

PS.... I wrote:

(Resubmitted with a more relevant example.)

On second thought, the (deleted) simpler example might be helpful.

Consider the following:

Note that the only difference between the formulas in B3 and B4 is the order of the addition.

Both B3 and B4 appear to be 1 when they are formatted to display 15 significant digits, which is all that Excel formats (rounded).

So B3=B4 in D3 returns TRUE.

But in fact, the binary value of B3 is infinitesimally different, as shown in C3.

So, ISNUMBER(MATCH(B3,B4,0)) returns FALSE in E3.

We might understand the difference if Excel displayed up to 17 significant digits, rounded.

C6:C8 display the exact decimal representation of the binary values of the constants in B6:B8, highlighting the first 15 and 17 significant digits.

And C3 displays the exact decimal representation of the binary value in B3.

The exact decimal values differ from the constants because most decimal fractions cannot be represented exactly in 64-bit binary floating-point. They must be approximated by the sum of 53 consecutive powers of 2. And the approximation of a particular decimal fraction might vary depending on the magnitude of the number. That is why, for example, 10.1 - 10 = 0.1 returns FALSE (!).

So, rounded to 17 significant digits, C3 would display 0.99999999999999989 instead of 1.

The difference between D3 (TRUE) and E3 (FALSE) is a misleading quirk of Excel (and work-alike apps).

For comparison operators (e.g. "="), Excel rounds both operands to 15 significant digit internally just for the purpose of the comparison.

Since B3 rounds to 1, it appears to be the same as B4.

But for match and lookup functions (and others), Excel compares the exact binary value.

Since the binary value of B3 is different, MATCH returns #N/A instead of 1.

As to exactly why the order of adding binary approximations of decimal fractions might cause different results, honestly the explanation is TMI.

In a nutshell, it has to do with "normalizing" and rounding internal 80-bit binary arithmetic to 64-bit binary results after each arithmetic operation.

Was this answer helpful?

1 person found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-12-03T18:23:51+00:00

    You're very welcome !

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-12-01T15:36:41+00:00

    Thank you so much for your answers, this helps a great deal.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-11-30T20:58:26+00:00

    (Resubmitted with a more relevant example.)

    Note that the difference is only in the least-significant decimal digit. And the binary difference might be even less.

    It is an anomaly of 64-bit binary floating-point arithmetic: the result can be order-sensitive.

    So, the averages differ presumably because the sum of A3:A6 + A9:A40 differs infinitesimally from the sum of A9:A40 + A3:A6.

    If you upload an Excel file with your data in A1:A40 to a file-sharing website and post a download URL that does not require that we log in, I can explain in detail.

    I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum. IMHO, do not use Google Drive. Many users confuse that with Google Sheets, which might alter the file.

    But I am able to demonstrate a similar result with a contrived subset of your data: A3:A6 and A9:A13 replicated 3 times (A9:A23).

    Image

    Formulas:

    C4: =AVERAGE(A3:A6, A9:A23)

    C5: =AVERAGE(A9:A23, A3:A6)

    E4: =C4=C5

    F4: =ISNUMBER(MATCH(C4,C5,0))

    C9: =SUM(A3:A6, A9:A23)

    C10: =SUM(A9:A23, A3:A6)

    E9: =C9=C10

    F9: =ISNUMBER(MATCH(C9,C10,0))

    Note that the averages in C4 and C5 actually differ by less than 1E-14, as demonstrated in D4 and D5.

    Note that the sums in C9 and C10 appear to be the same up to 15 significant digits. So, their comparison in E9 returns TRUE.

    But in fact, they differ by 2E-14 or less, as demonstrated in D9 and D10. So, a match in F9 returns FALSE.

    (The difference between the comparison operation in E9 and the match in F9 is a quirk of Excel and work-alike apps. It is non-standard.)

    PS.....

    Excel formats -- not "stores" -- only up to 15 significant digits, rounded, for displayed values.

    But Excel formats up to 17 significant digits, rounded, in XML files, which Excel uses to save as xlsx and xlsm.

    According to the IEEE 754 standard, 17 significant digits is necessary and sufficient to convert between decimal and binary with no loss of precision. But note that the 17-digit number is still just an approximation.

    Was this answer helpful?

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more