Handling Decimal Precision in Custom Formulas in SpreadJS

When working with custom formulas in SpreadJS, you may notice that a floating-point value returned from a custom function contains fewer decimal places compared to the same value entered directly into a cell.

For example, a custom function might return:

1.23456789012346

while the same value stored directly in a cell may display:

1.2345678901234567

This difference occurs due to internal precision handling.

Cause:

SpreadJS manages numerical precision internally for the following reasons:

JavaScript floating-point limitations
JavaScript follows the IEEE 754 Floating-Point Standard. Because of this, certain decimal values cannot be represented exactly in floating-point arithmetic. For example:

0.7 * 0.8

results in:

0.5599999999999999

Compatibility with Excel calculations
Microsoft Excel applies precision control when performing formula calculations. SpreadJS aligns with this behavior to ensure consistent calculation results.

Solution:

You can adjust the calculation precision in SpreadJS using the following API:

GC.Spread.CalcEngine.Functions.CALC_PRECISION = 17;

This setting controls the number of significant digits used during calculations but changing the precision value may affect other calculations in your workbook. It is recommended to adjust this setting carefully and thoroughly test formulas to ensure there are no unintended side effects.