Custom Field does not Round as expected. - wkb100025

ENVIRONMENT

• Empower
• Custom Fields

ANSWER

Customer has the following custom field to (Avgwt_FP) Truncate another custom field output to 2 d.p.         

Avgwt_FP               ROUND((Avg_wt_FP+0.005),-2)-0.01

Where                  Avg_wt_FP = Weight_Tabs/Num_of_Tabs 

Empower uses double-floating point numbers to internally represent values stord in Oracle.  However Oracle and Microsoft define floating-point values differently which can cause discrepancies. 

When a number is represented in some format that is not a native floating-point representation supported in a computer implementation, then it will require a conversion before it can be used in that implementation.

• If the number can be represented exactly in the floating-point format then the conversion is exact.
• If there is not an exact representation, then it is not possible to predict whether a number conversion will result in rounding up or down.

The expected value is:

142.940 + 0.005 = 142.945

which = 142.95 d.p.

142.95 - 0.01 = 142.94

When converting the decimal number to floating point and performing the truncate calculation:

This is the value given by Empower. 

ADDITIONAL INFORMATION

There are also differences in how Oracle and Windows handles values. For example Oracle uses Binary Coded Decimal (BCD) double-floating point format where as Windows uses IEEE double-floating point format. BCD is a more accurate method.

https://www.waters.com/waters/support.htm?lid=1851937&cid=511442&type=TECN

https://www.exploringbinary.com/floating-point-converter/

https://www.theregister.com/2006/08/12/floating_point_approximation/

https://en.wikipedia.org/wiki/Floating-point_arithmetic

https://docs.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info