I try to do simple round of a*b, if decimal is >= 0.5 then round up, if it less than 0.5 round down. I expected the result to be 15495, but somehow the result show 15494.

Do you have any suggestion of what I did wrong?[question.knwf|attachment]
(upload://1VRGNEBf1k9A870IrngPkRScXCX.knwf) (8.0 KB)

32.62 times 475 is not 15494.5 but 15494.49999999…

If you change the visualisation to Full Precision as showed in the snapshot above, you will see that this is the case. The Standard Double visualization by default is sometimes misleading in the case of “periodic decimals” or irrational numbers.

The HALF_UP function hence provides the expected result:

Complementary to my previous message, this problem with double numbers is not a problem with KNIME, but it is due to the way double numbers are coded and handled in binary format by computers. Often computers introduce tiny (0.0000000…0001) errors when converting and operating decimal numbers into binary format which may eventually lead to rounding errors. So for instance here:

32.62 times 475 is definitely 15494.5 for a human

but

32.62 times 475 is 15494.49999 (periodic) for a computer. It doesn’t help when using mathematical truncation or rounding functions, specially when you are at the edge of the truncation or rounding value.

In your case, a possible solution to implement, if you know the maximum number of decimals in your double number, is the following:

If for instance you are handling currency double numbers with cents (two decimals), then you could multiple each of your numbers by 100 and eventually divide the total by 10000. The result is then correct because binary conversion will not need to handle decimals:

It is quite difficult to explain the reason to business user since it is quite counter intuitive comparing to something like Excel. However, thanks a lot for your details explanation and work around, it works perfectly with my case.

I tested too in Java and it is the same, the result is correct.

On top of what I just said, I did further research and found the following thread:

RInt differs from the Round class in that it uses the Math.rint() method rather than Math.round(). Unlike Math.round(), in the case of a tie (e.g 1.5) Math.rint() prefers the nearest even integer (so rint(1.5) = 2). This has better statistical properties. For the two argument version you can consider it as a rounding method with a mode of round-half-even.

Apparently KNIME has adopted a -RInt- rounding scheme (or banking Scheme) to avoid “benefit or loss bias” in the long term when rounding. This is not intuitive and obvious to be understood.

Hope this clarifies possible issues or differences too.