Round issue

Question

Hi everyone,

My input data is shown in the table below. All fields are in Double format and the Output is calculated by the formula: A*B-C.

I want to keep two decimal places, but can anyone kindly tell me why the result is 1627.32 and not 1627.33? Thank you in advance!

ABCOutput41473.250.125201627.325

gautiergodard · Answer

Hey @LLXZ

Using the below is how I've solved this problem in the past:

round(round([Field 1],.001),.01)

Hope this helps!

Christina_H · Answer

Check out this thread for some possible solutions:

Solved: Rounding Discrepancies - Alteryx Community

philipmannering · Answer

Becuase computers aren't very good at storing decimal numbers. The result is stored as ≈ 1627.32499999 behind the scenes which rounds to 1627.32. You actually get a correct answer if you change your data types from Double to Float. This is because numbers with higher decimal places are more likely to encounter underflow / overflow errors.

Here's some more technical detail that I've copied and pasted...

When working with numbers in a computer, especially floating-point numbers, precision is a critical factor. Higher precision typically means more decimal places or more bits used to represent the number, which allows for greater accuracy in calculations. However, there are scenarios where increasing precision can actually result in incorrect answers due to rounding errors or other numerical artifacts. Here are some possible explanations for this phenomenon:

* Truncation and rounding errors: When performing calculations with floating-point numbers, which are approximations of real numbers, rounding errors can occur due to the finite precision of computer arithmetic. For example, if you perform a series of calculations with high-precision numbers and then round the result to a lower precision, you may lose accuracy and end up with an incorrect answer.

* Overflow and underflow: Higher precision can also lead to overflow or underflow issues. Overflow occurs when a number exceeds the maximum representable value for a given precision, while underflow occurs when a number becomes too small to be accurately represented. Both overflow and underflow can result in incorrect answers if not properly handled in computations.

* Numerical instability: Some numerical algorithms are inherently unstable, meaning that small errors in input data or intermediate results can lead to significantly inaccurate results. Higher precision may not necessarily fix the issue, as the instability may be intrinsic to the algorithm itself.

* Comparison and equality testing: When comparing or testing equality of floating-point numbers, higher precision can lead to unexpected results. Due to rounding errors, two seemingly equal numbers may not compare as equal with higher precision, resulting in incorrect answers in scenarios where precise comparison is required.