SOLVED

Incorrect result using average function

Copper Contributor

Hello,

When I use the average function on a list of 15-digit numbers (244 numbers), the displayed result is incorrect. Also, the result is not the same depending on how the numbers in my list are sorted. Have you encountered this problem before? Is there a solution? I contacted excel support, unfortunately they couldn't help me and sent me back here.

 

I suspect the problem is due to the length of the numbers.

 

Thanks.
Sarah

(text translated with google)

12 Replies

@OUEHSA01 

I propose the opposite.

AVERAGE() result is correct and does not depend on sorting order.

 

best response confirmed by OUEHSA01 (Copper Contributor)
Solution

@OUEHSA01 

Yes, the same, for example, for SUM(). Result could be different depends on sorting order and both could be different from manually calculated result.

 

I'm not ready to explain details, guess the reason is in calc engine precision for floating point operations plus function algorithm. Most probably similar is for any other spreadsheet software, not specifically for Excel. 

If I understand you correctly. it is normal for the result to vary. The result depends on on sorting order? Thanks

@OUEHSA01 

According to Excel specifications and limits:

S1673.png

So 15 digits is at the limit. Calculations involving such numbers may exceed that limit, but when and how depends on the order of the operations.

@OUEHSA01 

That's "normal" if we work near the limits. With 14 digit numbers most probably results will be the same.

@OUEHSA01  wrote:  ``the result is not the same depending on how the numbers in my list are sorted``

 

Yes.  But I would say "how the list is ordered", not "sorted".

 

-----

@Sergei Baklan  wrote:  ``With 14 digit numbers most probably results will be the same.``

 

No.  It actually has nothing to do with the decimal precision of the numbers.  For example:

 

SUM(-4.4, 79.89, -202.11, 126.62) returns -1.42E-14 (rounded)

SUM(79.89, -202.11, 126.62, -4.4) returns -8.88E-15 (rounded)

 

FYI, with -4.4, 79.89, -202.11, 126.62 in A1:A4, SUM(A1:A4) returns exactly zero for a completely different reason (tricks that Excel plays to try to hide such infinitesimal differences).

 

-----

@Hans Vogelaar  wrote:  ``According to Excel Specifications and Limits  [....] 15 digits is at the limit``

 

I know that you know that is wrong (wink).

 

The section that you cite confuses input limits and calculation limits, notwithstanding the section title.

 

For example, 9.99999999999999E+307 is an input limit, not a calculation limit.

 

Even MSFT "admits" that by adding that 1.7976931348623158E+308 is the "largest allowed positive number via formula" (aka calculation -- klunk!).

 

Likewise, 15 significant digits is an input limit, not a calculation limit.  It is also the maximum significant digits that Excel will display (rounded), even if we try to format more decimal places.

Thank you for your reply. I used google translate. Maybe my question wasn't clear enough :\ I don't really understand why the result is incorrect anymore. Maybe the result contains more than 15 digits and is rounded ? I don't know.

ERRATA....

 

@Sergei Baklan  wrote:  ``With 14 digit numbers most probably results will be the same.``

 

I wrote:  ``No.  It actually has nothing to do with the decimal precision of the numbers.``

 

I was thinking of non-integers.  But after re-reading @OUEHSA01's original posting, I realize that Sarah might be talking about 15-digit integers.  And Sergei might be talking about integers, as well.

 

With 24414-numbers, the minimum sum is 244*10000000000000, which is a 16-digit value.  Since that is less than 2^53 (*), Excel can indeed represent that sum exactly internally (although not always display correctly).

 

(* 2^53 is 9007199254740992, a 16-digit integer.  Excel can represent all integers from 0 to 2^53 exactly internally, although some are not displayed correctly.)

 

The maximum 244-integer sum is 24399999999999756 (244*99999999999999), which is a 17-digit value.  By coincidence, Excel can also represent that integer exactly internally.  But not all 17-digit integer (and some 16-digit integers) in between.

 

Nevertheless, Sergei has the right idea:  with 244 13-digit integers, the sum and average should not vary depending on order.

 

Mea culpa!

@Joe User 

Thank you for so detailed explanations. Yes, I mean integers.

 

Interesting result with average. I played with few hundred 15 digit numbers (integers). Average taken from calc engine is bit different if we sort (okay, order) table  in ascending or descending order.

In my case that is

'126,948,993,040,308.89 and

'126,948,993,040,309.55

within xml file (17 digits). Showing in cell results are rounded to 15 digits, thus we have 309 and 310 at the end accordingly.

 

I guess even with 14 digit integers we have less probability to have different results.

@OUEHSA01 

 

I apologize for any language difficulties.  I did not see your comment "translated by google".  I will try to be careful with my words.  Please feel free to ask for clarification if my words are not clear.

 

Some language differences to be aware of....  I use period (".") for a decimal point.  You might use comma (",").  Also, I use comma to separate parameters in Excel functions.  You might use semicolon (";").

 

When you say "15-digit numbers", do you mean only integers?  That is, do you mean only numbers of the form 123456789012345, not 1234567890.12345?

 

If so, then yes, the problem is with the number of digits (15).

 

Excel uses 64-bit binary floating-point to represent numbers.  That binary form can accurately represent all integers from 0 to 9007199254740992, a 16-digit integer.  After that, it can represent only some 16-digit integers and larger, and only by coincidence.

 

Moreover, Excel does not correctly display most integers with more than 15 digits (rounded).  Any additional digits to the right are replaced with zeros.

 

Late edit....  Even though each of your 15-digit integers is within those limits, their sum might exceed those limits.  Consequently, their sum might vary depending on order.  And if the sum varies, their average will likely vary as well.

 

-----

You ask:  ``Is there a solution?``

 

Generally, no.  You are dealing with a limitation of the internal representation (64-bit binary).

 

However, if you are willing to use VBA, you might be able to solve your problems with sum and average by using type Decimal.

 

And more generally, there are third-party add-ins that provide other functions that support "large numbers".

 

I cannot offer any recommendations, because I am not familiar with any of those add-ins.

 

Thank you for your explanations. I was indeed talking about integers. I should have made this clear.
Thank you very much for taking the time to explain to me how excel works! This is the first time I have encountered this situation. Now, I'll be careful when it happens.
1 best response

Accepted Solutions
best response confirmed by OUEHSA01 (Copper Contributor)
Solution

@OUEHSA01 

Yes, the same, for example, for SUM(). Result could be different depends on sorting order and both could be different from manually calculated result.

 

I'm not ready to explain details, guess the reason is in calc engine precision for floating point operations plus function algorithm. Most probably similar is for any other spreadsheet software, not specifically for Excel. 

View solution in original post