Oct 27 2023 01:11 AM
I would like to understand the logic behind these calculations. Please share some relevant resources.
Oct 27 2023 02:25 AM
See Floating-point arithmetic may give inaccurate results in Excel
Since your numbers have 2 decimal places, use
=ROUND(SUM(123.45,56.78,-180.23),2)
Oct 27 2023 04:50 AM
Oct 27 2023 10:20 AM - edited Oct 27 2023 11:30 PM
Solution
Your example is one that I provided in another answer to highlight this inconsistency in Excel.
@suraj786 wrote: ``When you perform the calculation 123.45+56.78-180.23 directly, Excel may be using a different method to perform the arithmetic``
Not exactly.
Internally, the same binary arithmetic is performed.
However, when a formula (not a subexpression) ends with a subtraction (or addition of a negative value) and the result is "close to zero" (purposely vague, and a misnomer), Excel sometimes arbitrarily replaces the actual arithmetic result with exactly zero.
This dubious feature was introduced in Excel 97 in a futile effort to hide such infinitesimal differences.
But this produces inconsistent results. For example, =123.45+56.78-180.23 displays exactly zero, but 123.45+56.78-180.23=0 returns FALSE(!).
Also, the "close to zero" heuristic is implemented inconsistently, especially internally in the SUM function.
For example, with 123.45, 56.78 and -180.23 in A1:A3, =SUM(A1:A3) returns exactly zero. So does =SUM(A1,A2,A3).
But as I demonstrated, =SUM(123.45,56.78,-180.23) correctly returns an infinitesimal difference, which is due to binary arithmetic anomalies.
This inconsistency is indeed specific to Excel. It is not the direct result of binary floating-point anomalies that are "a common issue in all computer systems". We do not see this inconsistency in VBA and Google Sheets (when the correct format is selected), for example.
PS.... Finally, the "close to zero" heuristic is not limited to differences that are literally close to zero.
For example, with 9E+99 in A1 and =9E+99 + 1E+85 in A2, =A2-A1 returns exactly zero (0.00E+00).
Yet, we can see a difference between A1 and A2; A2 displays 9.00000000000001E+99.
And A2=A1 returns FALSE.
Moreover, =SUM(A2,-A1) displays 9.71E+84 (rounded), the closest binary approximation of 1E+85 when added to 9E+99.
Obviously, 9.71E+84 is not "close to zero".
(It would be more correct to say that the heuristic applies when the difference is "close to zero relatively". But again, Excel is not consistent in its application of the heuristic.)
Oct 27 2023 10:20 AM - edited Oct 27 2023 11:30 PM
Solution
Your example is one that I provided in another answer to highlight this inconsistency in Excel.
@suraj786 wrote: ``When you perform the calculation 123.45+56.78-180.23 directly, Excel may be using a different method to perform the arithmetic``
Not exactly.
Internally, the same binary arithmetic is performed.
However, when a formula (not a subexpression) ends with a subtraction (or addition of a negative value) and the result is "close to zero" (purposely vague, and a misnomer), Excel sometimes arbitrarily replaces the actual arithmetic result with exactly zero.
This dubious feature was introduced in Excel 97 in a futile effort to hide such infinitesimal differences.
But this produces inconsistent results. For example, =123.45+56.78-180.23 displays exactly zero, but 123.45+56.78-180.23=0 returns FALSE(!).
Also, the "close to zero" heuristic is implemented inconsistently, especially internally in the SUM function.
For example, with 123.45, 56.78 and -180.23 in A1:A3, =SUM(A1:A3) returns exactly zero. So does =SUM(A1,A2,A3).
But as I demonstrated, =SUM(123.45,56.78,-180.23) correctly returns an infinitesimal difference, which is due to binary arithmetic anomalies.
This inconsistency is indeed specific to Excel. It is not the direct result of binary floating-point anomalies that are "a common issue in all computer systems". We do not see this inconsistency in VBA and Google Sheets (when the correct format is selected), for example.
PS.... Finally, the "close to zero" heuristic is not limited to differences that are literally close to zero.
For example, with 9E+99 in A1 and =9E+99 + 1E+85 in A2, =A2-A1 returns exactly zero (0.00E+00).
Yet, we can see a difference between A1 and A2; A2 displays 9.00000000000001E+99.
And A2=A1 returns FALSE.
Moreover, =SUM(A2,-A1) displays 9.71E+84 (rounded), the closest binary approximation of 1E+85 when added to 9E+99.
Obviously, 9.71E+84 is not "close to zero".
(It would be more correct to say that the heuristic applies when the difference is "close to zero relatively". But again, Excel is not consistent in its application of the heuristic.)