Why 4 equals 5?

[Rolf Hansmann]

Please calculate the following formular without any division in it:

=int((6.05-6)*100)

[#2]

Hello Rolf

That's the famous Floating Point Error.

Tutorial to Understand IEEE Floating-Point Errors

How To Implement Custom Rounding Procedures

Floating point (Wikipedia)

IEEE floating point (Wikipedia)

Floating Point Numbers - Computerphile (with Tom Scott)

[#3]

I thought a dividing operation is required to get an floating point error. This is a minus operation??

[#4]

Well, you thought incorrectly.

The kind of arithmetic operation does not matter.

[#5]

and what to do about it if it occurs at the 2 place after the decimal point and other programs like Spss or SAS are calculating it right. i might be wrong but for me it seems other programmers where able to avoid it by better implementing the algorithms or using a better library.

I calculated this with many programs and you find right and wrong.

[#6]

In Excel your are more or less limited to this:

How to correct rounding errors in floating-point arithmetic

[#7]

My $0.02

- My math skills don't allow me to understand why such behaviour couldn't be corrected, even with all references on IEEE standard.

- At least now we have no obvious bugs in floating point conversion which were few versions of Excel back, at least i didn't heard about them.

- Anyway, Microsoft reminds us about such issue from time to time, in addition to mentioned here links there is one more article which appeared just a month ago https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results...

- Thus the only we can do is to take into account this issue. IMHO, from recommended workarounds the "Precision as displayed" is not reliable. It works when, for example, you have in A1 the =(6.05-0.5)*100 and in B1 the = INT(A1). When B1 shows correct number. Not if you have entire formula in A1.

- for the interest checked how it works with other calculation engines in Excel environment. DAX in Power Pivot gives the same result as Excel.  M script in Power Query always gives correct result for all conversions i tried (like Int64.From(), etc.)

[#8]

Hi, I am not rounding, I use the integer operation. Ot just cuts everything after the decimal point. I strongly believe the minus operation calculates wrong at the second place after the decimal point. Financial institutions shoul better know it an not using Excel to calculate. Again, use Spss to calculate the same formula. It calculates correct.

[#9]

Hi, I am not rounding, I use the integer operation. It just cuts everything after the decimal point. I strongly believe the minus operation calculates wrong at the second place after the decimal point. Financial institutions shoul better know it an not using Excel to calculate. Again, use Spss to calculate the same formula. It calculates correct.