Discussion Re: Incorrect result using average function in Excel
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602642#M159316
<P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1485176">@OUEHSA01</a> </P><P> </P><P>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.</P><P> </P><P>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 (";").</P><P> </P><P>When you say "15-digit <U><EM>numbers</EM></U>", do you mean <U><EM>only integers</EM></U>? That is, do you mean <EM><U>only</U></EM> numbers of the form 123456789012345, not 1234567890<STRONG><FONT color="#DF0000">.</FONT></STRONG>12345?</P><P> </P><P>If so, then yes, the problem is with the number of digits (15).</P><P> </P><P>Excel uses 64-bit binary floating-point to represent numbers. That binary form can <U><EM>accurately</EM></U> represent all integers from 0 to 9007199254740992, a 16-digit integer. After that, it can represent <U><EM>only some</EM></U> 16-digit integers and larger, and only <U><EM>by coincidence</EM></U>.</P><P> </P><P>Moreover, Excel does not <U><EM>correctly</EM></U> display most integers with more than <U><EM>15</EM></U> digits (rounded). Any additional digits to the right are replaced with zeros.</P><P> </P><P>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.</P><P> </P><P>-----</P><P>You ask: ``Is there a solution?``</P><P> </P><P><FONT color="#DF0000">Generally, no.</FONT> You are dealing with a limitation of the internal representation (64-bit binary).</P><P> </P><P>However, if you are willing to use VBA, you might be able to solve your problems with sum and average by using type Decimal.</P><P> </P><P>And more generally, there are third-party add-ins that provide other functions that support "large numbers".</P><P> </P><P>I cannot offer any recommendations, because I am not familiar with any of those add-ins.</P><P> </P>Wed, 17 Aug 2022 21:59:23 GMTJoe User2022-08-17T21:59:23ZIncorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602182#M159230
<P>Hello,</P><P>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.</P><P> </P><P>I suspect the problem is due to the length of the numbers.</P><P> </P><P>Thanks.<BR />Sarah</P><P>(text translated with google)</P>Wed, 17 Aug 2022 13:18:35 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602182#M159230OUEHSA012022-08-17T13:18:35ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602204#M159233
<P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1485176">@OUEHSA01</a> </P><P>I propose the opposite.</P><P>AVERAGE() result is correct and does not depend on sorting order.</P><P> </P>Wed, 17 Aug 2022 13:34:56 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602204#M159233Detlef Lewin2022-08-17T13:34:56ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602236#M159236
<P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1485176">@OUEHSA01</a> </P>
<P>Yes, the same, for example, for SUM(). Result could be different depends on sorting order and both could be different from manually calculated result.</P>
<P> </P>
<P>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. </P>Wed, 17 Aug 2022 14:13:40 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602236#M159236Sergei Baklan2022-08-17T14:13:40ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602297#M159248
If I understand you correctly. it is normal for the result to vary. The result depends on on sorting order? ThanksWed, 17 Aug 2022 14:59:15 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602297#M159248OUEHSA012022-08-17T14:59:15ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602311#M159250
<P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1485176">@OUEHSA01</a> </P>
<P>According to <A href="https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3" target="_blank" rel="noopener">Excel specifications and limits</A>:</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="S1673.png" style="width: 473px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/396690i79E5450CEBC44A86/image-size/large/strip-exif-data/true?v=v2&px=999" role="button" title="S1673.png" alt="S1673.png" /></span></P>
<P>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.</P>Wed, 17 Aug 2022 15:08:42 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602311#M159250Hans Vogelaar2022-08-17T15:08:42ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602352#M159262
<P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1485176">@OUEHSA01</a> </P>
<P>That's "normal" if we work near the limits. With 14 digit numbers most probably results will be the same.</P>Wed, 17 Aug 2022 15:33:23 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602352#M159262Sergei Baklan2022-08-17T15:33:23ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602545#M159284
<P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1485176">@OUEHSA01</a> wrote: ``the result is not the same depending on how the numbers in my list are <FONT color="#DF0000">sorted</FONT>``</P><P> </P><P>Yes. But I would say "how the list is <FONT color="#DF0000">ordered</FONT>", not "sorted".</P><P> </P><P>-----</P><P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/521">@Sergei Baklan</a> wrote: ``With 14 digit numbers most probably results will be the same.``</P><P> </P><P>No. It actually has nothing to do with the <EM><U>decimal</U></EM> precision of the numbers. For example:</P><P> </P><P>SUM(-4.4, 79.89, -202.11, 126.62) returns -1.42E-14 (rounded)</P><P>SUM(79.89, -202.11, 126.62, -4.4) returns -8.88E-15 (rounded)</P><P> </P><P>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).</P><P> </P><P>-----</P><P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/127945">@Hans Vogelaar</a> wrote: ``According to <A href="https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3" target="_self">Excel Specifications and Limits</A> [....] 15 digits is at the limit``</P><P> </P><P>I know that you know that is wrong (wink).</P><P> </P><P>The section that you cite confuses <U><EM>input</EM></U> limits and <U><EM>calculation</EM></U> limits, notwithstanding the section title.</P><P> </P><P>For example, 9.99999999999999E+307 is an <EM><U>input</U></EM> limit, not a <U><EM>calculation</EM></U> limit.</P><P> </P><P>Even MSFT "admits" that by adding that 1.7976931348623158E+308 is the "largest allowed positive number <FONT color="#DF0000">via formula</FONT>" (aka <FONT color="#DF0000">calculation</FONT> -- klunk!).</P><P> </P><P>Likewise, 15 significant digits is an <U><EM>input</EM></U> limit, not a <EM><U>calculation</U></EM> limit. It is also the maximum significant digits that Excel will <U><EM>display</EM></U> (rounded), even if we try to format more decimal places.</P>Wed, 17 Aug 2022 19:24:44 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602545#M159284Joe User2022-08-17T19:24:44ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602557#M159286
Thank you for your reply. I used google translate. Maybe my question wasn't clear enough <LI-EMOJI id="lia_confused-face" title=":confused_face:"></LI-EMOJI> 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.Wed, 17 Aug 2022 19:27:21 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602557#M159286OUEHSA012022-08-17T19:27:21ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602606#M159297
<P>ERRATA....</P><P> </P><P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/521">@Sergei Baklan</a> wrote: ``With 14 digit numbers most probably results will be the same.``</P><P> </P><P>I wrote: ``No. It actually has nothing to do with the <EM><U>decimal</U></EM> precision of the numbers.``</P><P> </P><P>I was thinking of <FONT color="#DF0000">non-integers</FONT>. But after re-reading <a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1485176">@OUEHSA01</a>'s original posting, I realize that Sarah might be talking about 15-digit <FONT color="#DF0000">integers</FONT>. And Sergei might be talking about <FONT color="#DF0000">integers</FONT>, as well.</P><P> </P><P>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).</P><P> </P><P>(* 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.)</P><P> </P><P>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.</P><P> </P><P>Nevertheless, Sergei has the right idea: with 244 <FONT color="#DF0000">13</FONT>-digit <FONT color="#DF0000">integers</FONT>, the sum and average should not vary depending on order.</P><P> </P><P>Mea culpa!</P>Thu, 18 Aug 2022 13:02:16 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602606#M159297Joe User2022-08-18T13:02:16ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602634#M159312
<P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/146717">@Joe User</a> </P>
<P>Thank you for so detailed explanations. Yes, I mean integers.</P>
<P> </P>
<P>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.</P>
<P>In my case that is</P>
<P>'126,948,993,040,308.89 and</P>
<P>'126,948,993,040,309.55</P>
<P>within xml file (17 digits). Showing in cell results are rounded to 15 digits, thus we have 309 and 310 at the end accordingly.</P>
<P> </P>
<P>I guess even with 14 digit integers we have less probability to have different results.</P>Wed, 17 Aug 2022 21:10:27 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602634#M159312Sergei Baklan2022-08-17T21:10:27ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602642#M159316
<P><a href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/1485176">@OUEHSA01</a> </P><P> </P><P>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.</P><P> </P><P>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 (";").</P><P> </P><P>When you say "15-digit <U><EM>numbers</EM></U>", do you mean <U><EM>only integers</EM></U>? That is, do you mean <EM><U>only</U></EM> numbers of the form 123456789012345, not 1234567890<STRONG><FONT color="#DF0000">.</FONT></STRONG>12345?</P><P> </P><P>If so, then yes, the problem is with the number of digits (15).</P><P> </P><P>Excel uses 64-bit binary floating-point to represent numbers. That binary form can <U><EM>accurately</EM></U> represent all integers from 0 to 9007199254740992, a 16-digit integer. After that, it can represent <U><EM>only some</EM></U> 16-digit integers and larger, and only <U><EM>by coincidence</EM></U>.</P><P> </P><P>Moreover, Excel does not <U><EM>correctly</EM></U> display most integers with more than <U><EM>15</EM></U> digits (rounded). Any additional digits to the right are replaced with zeros.</P><P> </P><P>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.</P><P> </P><P>-----</P><P>You ask: ``Is there a solution?``</P><P> </P><P><FONT color="#DF0000">Generally, no.</FONT> You are dealing with a limitation of the internal representation (64-bit binary).</P><P> </P><P>However, if you are willing to use VBA, you might be able to solve your problems with sum and average by using type Decimal.</P><P> </P><P>And more generally, there are third-party add-ins that provide other functions that support "large numbers".</P><P> </P><P>I cannot offer any recommendations, because I am not familiar with any of those add-ins.</P><P> </P>Wed, 17 Aug 2022 21:59:23 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602642#M159316Joe User2022-08-17T21:59:23ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602652#M159322
Thank you for your explanations. I was indeed talking about integers. I should have made this clear.Wed, 17 Aug 2022 21:33:02 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602652#M159322OUEHSA012022-08-17T21:33:02ZRe: Incorrect result using average function
https://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602663#M159324
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.Wed, 17 Aug 2022 21:48:29 GMThttps://techcommunity.microsoft.com/t5/excel/incorrect-result-using-average-function/m-p/3602663#M159324OUEHSA012022-08-17T21:48:29Z