Excel decimal fractions

[SanthoshKunder]

Can someone explain why these anomalies occur with the same arithmetic operation in excel?

1. =SUM(123.45,56.78,-180.23) returns 2.8421709430404E-14
2. = 123.45+56.78-180.23 returns 0

I would like to understand the logic behind these calculations. Please share some relevant resources.

@Joe User ; @Riny_van_Eekelen;@Hans Vogelaar;@Sergei Baklan

[#2]

@SanthoshKunder 

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)

[#3]

The difference in results you’re seeing is due to the way Excel handles floating-point arithmetic and the precision of numbers.

In Excel, as in all computer systems, numbers are stored with a certain level of precision. When you perform calculations, this precision can lead to very small rounding errors. In your case, the SUM function is returning a result that is very close to zero, but not exactly zero due to these rounding errors.

The number 2.8421709430404E-14 is a very small number close to zero. The E-14 part means that the decimal point is moved 14 places to the left. So, the actual number is 0.000000000000028421709430404, which is almost zero.

When you perform the calculation 123.45+56.78-180.23 directly, Excel may be using a different method to perform the arithmetic that doesn’t result in the same level of rounding error.
This is a common issue in all computer systems when dealing with floating-point numbers and not specific to Excel. It’s always important to be aware of this when performing precise calculations.

[#4 · Accepted solution]

@SanthoshKunder 

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.)