Excel precision issue?

[cybpsych]

guys,

i noticed something very peculiar.

this is a simple arithmetic for simple numbers: 624 minus 623.8 will give result of 0.2000000000000450000000000000000..............

i know excel have precision issue but for a simple number, the results should be as simple as 0.2, right?

how to "fix" this?

note: Using Excel 2019 build 1812 x64 bit

 

[Ron Coderre]

Computers work with binary representations of numbers, but humans work with decimal numbers. There are some decimal numbers that cannot be exactly represented in binary. It's the same kind of issue that prevents the fraction 1/3 from being exactly represented as a decimal number.

Microsoft has this website on "How to correct rounding errors in floating-point arithmetic":
http://support.microsoft.com/kb/214118

Floating-point arithmetic may give inaccurate results in Excel:
http://support.microsoft.com/kb/78113/en-us

Does that help?

 

[cybpsych]

Computers work with binary representations of numbers, but humans work with decimal numbers. There are some decimal numbers that cannot be exactly represented in binary. It's the same kind of issue that prevents the fraction 1/3 from being exactly represented as a decimal number.

Microsoft has this website on "How to correct rounding errors in floating-point arithmetic":
http://support.microsoft.com/kb/214118

Floating-point arithmetic may give inaccurate results in Excel:
http://support.microsoft.com/kb/78113/en-us

Does that help?

thank Ron, but no changes if "Set precision as displayed".

also, the kb explains issue with very large or very small number, which is understandable

my curiosity is with a fairly "normal" number, where it shoudnt be any precision error.

anyways, just curious about this

thanks!

 

[Peter Bartholomew]

What comprises a 'normal' number depends upon what base you choose for your counting (that is how many unique digits you use before you run out of digits and move up a place to 10, i.e. one times the base plus zero units).
In decimal arithmetic 0.2 (i.e. 1/5) is easy to represent [1/3 and 1/7 are not 'easy' in that they have no exact decimal representation] but in binary arithmetic it is 1/2, 1/4 etc. that may be stored precisely.

1/5 becomes an infinite sequence starting with 3/16, followed by 3/16² and so on indefinitely. The computer representation truncates this a some point, which means the 623.8 was never exactly that in the first place. The inevitable error then propagates through the calculation chain in a reasonably predictable manner.

Stored as binary 0.2 becomes
0.00110011001100110011001100110011...