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

[rodp]

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

	A	B	C	D
1	2013.13
2	2013
3	0.13
4	=A1-A2	This will show a result of 0.13 but in fact it's 0.130000000000109000 <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

Thanks in advance

RodP

 

[rodp]

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

 

[shg]

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

 

[rodp]

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