hyperformula
hyperformula copied to clipboard
handsontable
#CYCLE! shouldn't occur when the IF condition is not met
Description
If we have an IF function where the conditions that lead to #CYCLE! error isn't met we will get an error anyway. All other spreadsheets I've checked, GS, XL, LC, handles this situation differently. When the condition for circular reference is met the GS will show #REF with a description about circular reference, XL will display a pop-up, LC will show Err:522 which is described as "522 Circular reference"[1].
HF will always return #CYCLE! error. Doesn't matter if the condision is met or not.
Steps to reproduce
This test should pass
it('circular dependency when the IF condition is not met', () => {
const engine = HyperFormula.buildFromArray([
['0', '=IF($A$1=0,0,B1)'],
['1', '=IF($A$2=0,0,B1)'],
])
expect(engine.getCellValue(adr('B1'))).toEqual(0)
expect(engine.getCellValue(adr('B1'))).toEqual(detailedError(ErrorType.CYCLE))
})
Links
[1] https://help.libreoffice.org/6.2/en-US/text/scalc/05/02140000.html
By design. ATM no plans to change the behavior, because it would affect the performance.
We should also remember, that this would require revision of the main assumptions of the design of HyperFormula. It seems possible, but we should expect a big change in the engine.
Even simpler example presenting this issue:
it('circular dependency when the IF condition is not met', () => {
const engine = HyperFormula.buildFromArray([
['5', '=IF(A1=0,B1,A1)'],
])
expect(engine.getCellValue(adr('B1'))).toEqual(5)
})
as currently HF will return #CYCLE error for B1.
I get the following results:
this.hf.setCellContents({ sheet: this.mainDT, col: 0, row: 0 }, "=B1");
this.hf.setCellContents({ sheet: this.mainDT, col: 1, row: 0 }, "=A1");
this.hf.setCellContents({ sheet: this.mainDT, col: 2, row: 0 }, "=IF(FALSE,1,A1)");
this.printCellData("C1", this.mainDT);// cycle
this.hf.setCellContents({ sheet: this.mainDT, col: 2, row: 0 }, "=IF(TRUE,1,A1)");
this.printCellData("C1", this.mainDT);// no cycle
so it works as in excel?
Yes, this works. But the problem is when the IF cell depends on different cells depending on the evaluation of the expression and only one of them leads to the cycle. Then we always return the cycle, no matter what is the value of the logical expression.
Please see the examples above.
