Why 100 - 98.362 = 1.638000..010...?

[N8HJOH]

I get lots of help from here. It's about time I get help by asking a question instead of just looking up.

I have below a value and a formula entered in the cells as below.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]98.362[/TD]
[TD]=100-B1[/TD]
[/TR]
</tbody>[/TABLE]

And, the value returned in the cell, B1, is 1.63800000000001, not 1.638.

Has anyone seen this?
How can this be fixed?

My remedy is to use the formula, =100-B1-10^-14, just for this operation for now.
It seems so bizarre. Who knew subtraction can be more than meets the eye?

 

[offthelip]

This is normal behavious for EXCEl , it is normally only accurate to 15 significant figures which is exasctly what you have got, you might find it interestingto read this wikipedia article:
https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

 

[jtakw]

Hi,

Also, you can use ROUND function to whatever decimal/digits you want:

Book1
	A	B
1	98.362	1.638
Sheet29

Cell Formulas
Range	Formula
B1	=ROUND(100-A1,3)

 

[MARK858]

My remedy is to use the formula, =100-B1-10^-14, just for this operation for now.
It seems so bizarre. Who knew subtraction can be more than meets the eye?

Does

=100-ROUND(A1,3)

not work for you rather than

=100-B1-10^- 14

Btw I am assuming that your formula should have been A1 rather than B1.

 

[N8HJOH]

=100-A1 is correct. Round should work. Looks like I could add this as another example to the referred wiki page.

Thanks, all, for your help!

 

[jtakw]

Just FYI, there's a difference between Rounding Before and After the subtraction:

Cells formatted to show 14 digits after decimal:

Book1
	A	B	C
1	98.362	1.63800000000000	1.63800000000001
Sheet29

Cell Formulas
Range	Formula
B1	=ROUND(100-A1,3)
C1	=100-ROUND(A1,3)

 

[MARK858]

Just FYI, there's a difference between Rounding Before and After the subtraction:

Cells formatted to show 14 digits after decimal:

Noted