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.
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!
- Thank you for your explanations. I was indeed talking about integers. I should have made this clear.
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.
