# Number not the same as a calculated number, (why doesn't (2013.13 - 0.13) equal 0.13)

#### rodp

##### Board Regular
Hi All,

I have a strange issue occuring when I try to test when a number equals another number. At first I thought it was due to the function i was using [INT() or ROUND()] but have disproved this by doing this manually. Please could someone help me understand if this is a general issue or whether it's something to do with my PC? I'm using excel 2007.

In Excel I have 2 numbers, 2013.13 and 2013 in separate cells. In another cell I simply subtract them. This should bring an answer of 0.13 but if you extend the number of decimal place to 13 you start to see some extra digits, namely 0.130000000000109000. The problem with this then means you can't test if the subtraction is the same as a fixed test number.

Here's the table
ABCD
12013.13
22013
30.13
4=A1-A2This will show a result of 0.13 but in fact it's
 0.13

<tbody>
</tbody>
If(A3=A4,TRUE, FALSE)This should return TRUE but because of the extra digits occuring then it returns FALSE... hence my problem!

<tbody>
</tbody>

Hope someone can help

RodP

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
ah! just found something: Errors When Subtracting (Microsoft Excel)

That might help but the problem is will that setting stick with the file in question as this is going to have to be used on other people's PCs?

Thanks

Rodp

Using Precision As Displayed has major ramifications; there are few situations I've seen where it is appropriate. Judicious rounding is almost always a better solution:

 A​ B​ 1​ 2013.13​ A1: Input 2​ 2013​ A2: Input 3​ 0.13​ A3: Input 4​ TRUE​ A4: =ROUND(A3,6)=A3

Hi All,

I used Round() in the end - now understand what floating points are all about .... you would have thought they would have solved that problem by now - how on earth can you work at the nth decimal place knowing that?! Hope Nasa aren't using excel for their sums or their little spaceship that's going to land on that comet in November might not end up successful!!

Cheers

Rodp

Replies
14
Views
432
Replies
2
Views
242
Replies
6
Views
1K
Replies
4
Views
484
Replies
9
Views
451

1,221,241
Messages
6,158,726
Members
451,512
Latest member
aimarhu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back