+ Reply to Thread
Results 1 to 6 of 6

Excel Multiplication Miscalculation

  1. #1
    Forum Contributor
    Join Date
    06-03-2008
    Posts
    387

    Excel Multiplication Miscalculation

    Please Login or Register  to view this content.
    The above formula miscalulates to be:
    0.0710000000000002

    When it should be:
    0.071

    Does anyone know why this occurs?

    I did a little more research, and it appears that taking 1.071 minus anything greater than 0.971 creates this miscalculation.
    Last edited by yawnzzzz; 12-02-2011 at 05:13 PM.

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,173

    Re: Excel Multiplication Miscalculation

    Number formatting issue?

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,368

    Re: Excel Multiplication Miscalculation

    Hi,

    You are running into the well known behaviour associated with floating point arithmetic.
    For a fuller explanation see http://support.microsoft.com/kb/78113

    To resolve wrap your formula in an =ROUND() function.
    i.e.
    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Excel Multiplication Miscalculation

    Nope - I checked, and the poster is correct.
    1.05*1.02=1.07100000000000000000000
    1.071 -1 = .071

    =((1.05)*(1.02))-1 yields 0.0710000000000002

    Not sure why this is, but it's definitely a miscalc.
    If your question has been satisfactorily addressed, please consider marking it solved. Click the Thread Tools dropdown and select Mark thread as solved.
    Also, you might want to add to the user's reputation by clicking the star icon in the lower left corner of the post with the answer- it's why we do what we do...

    Thomas Lafferty
    Analyst/Programmer

  5. #5
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,173

    Re: Excel Multiplication Miscalculation

    ohhhhhh that. My bad. At work, so sometimes I read the post the too fast. Yup floating point issue. Same thing sometimes happen with Access and SQL too.

  6. #6
    Forum Contributor
    Join Date
    06-03-2008
    Posts
    387

    Re: Excel Multiplication Miscalculation

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    You are running into the well known behaviour associated with floating point arithmetic.
    For a fuller explanation see http://support.microsoft.com/kb/78113

    To resolve wrap your formula in an =ROUND() function.
    i.e.
    Please Login or Register  to view this content.
    Thanks, that was a very interesting article. It appears this section relates to my scenario:
    "Repeating Binary Numbers and Calculations with Results Close to Zero
    Another confusing problem with storing floating point numbers in binary is that some numbers, which are finite, non-repeating numbers in decimal base 10, are infinite, repeating numbers in binary. The most common example of this is the value 0.1 and variants thereof. Although these numbers can be represented perfectly in base 10, the same number in binary format becomes the following repeating binary number when stored in the mantissa: 000110011001100110011 (and so on) "

    It also mentions that Excel can only handle 15 points of precision, so I believe the most accurate solution is this:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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