Formulas With Array Inputs Do Not Recompute On Changes To Referred Cells In The Array

[ghost]

Description

Actual: If I define a formula using an array, the value of the formula is only computed initially.
Expected: If I define a formula using an array, the value of the formula should recompute whenever the value of the array changes or the value of a cell referred to by the array changes.

Steps to reproduce

1. Define a formula using an array where the array contains references to other cells
2. Get the value of the formula
3. Alter the value of the referred cell
4. Get the value of the formula again and see that it did not change

Demo

Here's a unit test in Typescript and Jest

test('should recalculate formulas that depend on arrays', () => {
  const hyperFormula: HyperFormula = HyperFormula.buildFromArray(
    [[1, 2, 3, '=SUM({A1, B1, C1})']],
    {licenseKey: 'gpl-v3'},
  );
  expect(hyperFormula.getCellValue({row: 0, col: 3, sheet: 0})).toBe(1 + 2 + 3);
  hyperFormula.setCellContents({row: 0, col: 0, sheet: 0}, 2);
  expect(hyperFormula.getCellValue({row: 0, col: 3, sheet: 0})).toBe(2 + 2 + 3);
});

The second expect fails with

Expected: 7
Received: 6

when it should succeed.

Your environment

Hyperformula: 2.0.0
Browser: Chrome Version 100.0.4896.127 (Official Build) (64-bit)
Operating System: Ubuntu 21.10

Links

• Related Bug: Inline arrays don't watch their dependencies (previously: Volatile formulas not recalculating array dependencies) #894

[sequba]

@Chris-Schuster-Daydream I am sorry you experienced an issue with HyperFormula. Our team will investigate it shortly.

[sequba]

@Chris-Schuster-Daydream Is there a reason why you use curly braces ({...}) inside SUM function instead of a regular range? E.g.:

=SUM(A1:C1)

Explanation

Curly braces syntax defines an ad-hoc array (inline array, array constant) which is meant to be used for constant expressions only.

From Open Formula 1.3 Standard:

Evaluators that support inline arrays shall accept a matrix with one or more rows, each with one or more columns, with the same number of columns in each row, with constant values for each expression. [...]
Note: Expression authors should be aware that use of Expression other than constant Number or constant String may impair interoperability.

Other software

Spreadsheet software is not entirely consistent in handling cell references inside inline arrays. Typing =SUM({A1, B1, C1}) results in:

Microsoft Excel	LibreOffice Calc	Google Sheets	HyperFormula
ERROR	ERROR	value calculated and updated on changes	value calculated once but no updates

@warpech What do you think about this case? I'd definitely describe ad-hoc arrays better in our docs but should we alter the behaviour of HyperFormula to match the other software?

[ghost]

@sequba
Originally, I was using this for operating over non-contiguous ranges. Here's a more true to life unit test (that is still simplified for the purposes of communicating the idea).

  test('should recalculate formulas that depend on noncontiguous arrays', () => {
    const hyperFormula: HyperFormula = HyperFormula.buildFromArray([
      [1, 'unused', 2, 'unused', 3, '=SUMIF({A1, C1, E1}, ">0")'],
    ], {licenseKey: 'gpl-v3'});
    expect(hyperFormula.getCellValue({row: 0, col: 5, sheet: 0})).toBe(1 + 2 + 3);
    hyperFormula.setCellContents({row: 0, col: 0, sheet: 0}, -1);
    expect(hyperFormula.getCellValue({row: 0, col: 5, sheet: 0})).toBe(2 + 3);
  });

This one fails on the second expect with

Expected: 5
Received: 6

And here's an even more realistic example where I was trying to sum arbitrary fractions so long as the denominator wasn't zero:

  test('should sum fractions with non-zero denominators', () => {
    const hyperFormula: HyperFormula = HyperFormula.buildFromArray([
      [10, 'unused', 20, 'unused', 30, '=SUMIF({B2, D2, F2}, ">0", {A1/B2, C1/D2, E1/F2})'],
      ['unused', 0, 'unused', 4, 'unused', 6],
    ], {licenseKey: 'gpl-v3'});
    expect(hyperFormula.getCellValue({row: 0, col: 5, sheet: 0})).toBe((20 / 4) + (30 / 6));
    hyperFormula.setCellContents({row: 0, col: 1, sheet: 0}, 2);
    expect(hyperFormula.getCellValue({row: 0, col: 5, sheet: 0})).toBe((10 / 2) + (20 / 4) + (30 / 6));
  });

This one fails on the second expect with:

Expected: 15
Received: 10

[warpech]

@warpech What do you think about this case? I'd definitely describe ad-hoc arrays better in our docs but should we alter the behaviour of HyperFormula to match the other software?

Altering it would be a breaking change. Let's collect more evidence if users are strongly convinced that the feature should be changed.

For now, I think we should:

1. cement the current behavior by testing that the changes are not recomputed
2. explain it better in the docs, as you proposed

[sequba]

Issue #984

[sequba]

@Chris-Schuster-Daydream With HyperFormula 2.0.1 we updated our documentation to explain inline arrays better: https://handsontable.github.io/hyperformula/guide/arrays.html#inline-arrays

[sequba]

@Chris-Schuster-Daydream All things considered, we decided that a new configuration flag will be introduced to control this behaviour. So it will be possible to set up HyperFormula in such a way that inline arrays are recomputed on updates to their dependencies. This task will be tracked in issue #894

[ghost]

That's good news to hear. Thanks.