# Excel Multiplication Miscalculation

1. ## 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.

2. ## Re: Excel Multiplication Miscalculation

Number formatting issue?

3. ## 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.``

4. ## 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.

5. ## 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. ## Re: Excel Multiplication Miscalculation

Originally Posted by Richard Buttrey
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.``

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