NUMBER type precision is not precise enough

[wojciechczerniak]

Description

Engine inherits precision errors from Javascript. It should use the language and its limitations to provide results that are expected by final users. 0.1 + 0.2 that does not equal 0.3 isn't the best result we can provide.

Javascript has a precision of 17 significant number, spreadsheet apps require precision of 15. Additionally, they use an algorithm called "snap to zero". See resources.

Issue 1: Engine doesn't handle Javascript arithmetics quirks

it('should correctly calculate 0.2 + 0.1 as 0.3', () => {
  const engine = HyperFormula.buildFromArray([
    ['=0.2+0.1'],
  ])

  expect(engine.getCellValue(adr('A1'))).toBe(0.3)
})

Result:

Expected: 0.3
Received: 0.30000000000000004

Issue 2: Visual rounding is not an option, the error propagates to comparison operator

 it('works for obvious case', () => {
    const engine = HyperFormula.buildFromArray([
      ['=0.2+0.1', '=IF(A1=0.3, "True", "False")'],
    ])

    expect(engine.getCellValue(adr('B1'))).toBe('Excel')
  })

Result:

Expected: "True"
Received: "False"

Issue 3: Should handle repeating decimal

it('should handle 1/3 correctly', () => {
  const engine = HyperFormula.buildFromArray([
    ['=1/3', '=A1*3'],
  ])

  expect(engine.getCellValue(adr('A1'))).toBe(0.33333333333333300000)
  expect(engine.getCellValue(adr('B1'))).toBe(1.00000000000000000000)
})

Issue 4: Should handle decimal that looks like repeating decimal but it isn't one

it('0.33333333333333300000 is not the same as 1/3', () => {
  const engine = HyperFormula.buildFromArray([
    ['0.333333333333333', '=A1*3'],
  ])

  expect(engine.getCellValue(adr('A1'))).toBe(0.33333333333333300000)
  expect(engine.getCellValue(adr('B1'))).toBe(0.99999999999999900000) // Excel
  expect(engine.getCellValue(adr('B1'))).toBe(1.00000000000000000000) // Calc
})

Warning: There is a difference between other spreadsheets. Differences are described somewhere within resources links.

Resoruces

[1] https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
[2] http://www.gnumeric.org/numerical-issues.html
[3] https://support.google.com/docs/forum/AAAABuH1jm0Kc6fcJAsfok/?hl=en
[4] https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel
[5] http://cpearson.com/Excel/rounding.htm

Documentation

• We should create a documentation page that explains precision
• A section that describes differences in precision between spreadsheets would be helpful

[wojciechczerniak]

Libs thay may be usefull to solve this issue:

• https://github.com/MikeMcl/big.js
• https://github.com/MikeMcl/bignumber.js/
• https://github.com/MikeMcl/decimal.js/

[wojciechczerniak]

We've agreed to add an option to settings #58 which will enable comparison approximation.

From section 6.4.7 Infix Operator "=" we have Evaluators may approximate and test equality of two numeric values with an accuracy of the magnitude of the given values scaled by the number of available bits in the mantissa, ignoring some least significant bits and thus providing compensation for not exactly representable values and from the 2006 draft comments Note: It'd be nice to devise a more specific definition of "=" for "nearly equal numbers", but after discussion, we were unable to agree on one.

[izulin]

done, #120