+ Reply to Thread
Results 1 to 3 of 3

Difference between two exact same figures

  1. #1
    Registered User
    Join Date
    10-09-2018
    Location
    hong kong
    MS-Off Ver
    2013
    Posts
    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. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,798

    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. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    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.)
    Last edited by joeu2004; 10-09-2018 at 01:58 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 08-06-2018, 12:26 PM
  2. filtering exact match figures with opposite sign
    By yutoyumi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-25-2017, 04:23 AM
  3. Difference Between 2 Highest Figures when Filtering
    By jenna_max in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-24-2014, 01:50 AM
  4. [SOLVED] Conditional formatting based on the difference value between figures in two cells
    By Karczoch in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-10-2014, 07:50 AM
  5. Calculating the difference between monthly figures
    By cazz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-24-2013, 01:59 PM
  6. Conversion of an array of figures inputted into cells as word figures to Excel figures
    By Allan Simpson730 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-08-2013, 07:25 AM
  7. How to calculate exact date difference in Excel?
    By xlsQ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-14-2012, 05:46 PM

Bookmarks

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