# Difference between two exact same figures

1. ## Difference between two exact same figures

20181009.png

These two figures (A) and (B) are generated from two different models but the figures should be the same, but somehow there is difference between the two, anyone can help?

2. ## Re: 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

3. ## Re: Difference between two exact same figures

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1