Forum Discussion
Solved
Incorrect result using average function
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)
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.
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".
-----
SergeiBaklan 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).
-----
HansVogelaar 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.
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.
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
That's "normal" if we work near the limits. With 14 digit numbers most probably results will be the same.
