Why doesn't my Empower Custom Field Round as expected? - WKB100025

ENVIRONMENT

• Empower

ANSWER

The following custom field (Avgwt_FP) truncates another custom field to two decimal places.         

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

Where                  Avg_wt_FP = Weight_Tabs/Num_of_Tabs 

The expected value is:

142.940 + 0.005 = 142.945

which = 142.95 to two decimal places.

142.95 - 0.01 = 142.94

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

This is the value provided by Empower. 

ADDITIONAL INFORMATION

To avoid potential issues please review the guidance provided in WKB72986 - Best practices for custom fields in Empower

Empower uses double-precision floating point numbers to internally represent values stored in Oracle.  However, Oracle and Microsoft define floating-point values differently, which can cause discrepancies. 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.

Computers store floating-point numbers in a binary (base-2) format that approximates them rather than representing them exactly as the base-10 numbers we are used to.  Floating point numbers are stored using a fixed number of bits; they can’t always hold the exact value and must be rounded or truncated.  When the binary number can not exactly represent the number, this conversion results in the number being rounded up or rounded down. It is not possible to predict whether a number conversion will result in rounding up or down.

There are also differences in how Oracle and Windows handles values. For example

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