Aug 17 2022 06:18 AM
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)
Aug 17 2022 06:34 AM
Aug 17 2022 07:13 AM
SolutionYes, 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.
Aug 17 2022 07:59 AM
Aug 17 2022 08:08 AM
According to Excel specifications and limits:
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.
Aug 17 2022 08:33 AM
That's "normal" if we work near the limits. With 14 digit numbers most probably results will be the same.
Aug 17 2022 12:10 PM - edited Aug 17 2022 12:24 PM
@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.
Aug 17 2022 12:27 PM
Aug 17 2022 01:34 PM - edited Aug 18 2022 06:02 AM
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!
Aug 17 2022 02:10 PM
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.
Aug 17 2022 02:15 PM - edited Aug 17 2022 02:59 PM
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.
Aug 17 2022 02:33 PM
Aug 17 2022 02:48 PM