Forum Discussion
Incorrect result using average function
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.
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.
ERRATA....
SergeiBaklan 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!
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.
