Difference between two exact same figures

Have a look at https://www.excel-easy.com/examples/...nt-errors.html
and at
https://support.microsoft.com/en-us/...sults-in-excel

They only appear to be equal because: (1) Excel formats only up to the first 15 significant digits (rounded); and (2) the comparison operators (=, <>, etc) compare only the first 15 significant digits (rounded) of the left and right operands.

So although A1=A2 returns TRUE, A1-A2=0 might return FALSE, and MATCH(A1,A2,0) might return #N/A, indicating no match.

The work-around is: whenever you expect a calculation to be accurate to some number of decimal places, explicitly round (usually using ROUND) to that number of decimal places. (Not to an arbitrary number of decimal places like 10, as some people suggest.)

-----

Contrary to what you might read, Excel does not "store" numbers up to 15 significant decimal digits.

Excel uses 64-bit binary floating-point to represent numeric values as a sum of 53 consecutive powers of two ("bits"). Consequently, most decimal fractions cannot be represented exactly. And the approximation of a particular decimal fraction varies with the magnitude of the integer part.

That often causes infinitesimal arithmetic differences when compared to decimal arithmetic on paper. For example, IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!) because (showing the decimal conversion of the exact internal representation on the right):

Please Login or Register  to view this content.

(I use period for the decimal place and comma to demarcate the first 15 significant digits.)