Error N/A with some operators gives wrong result

[aninde]

Description

There are some differences in the results between HyperFormula and Excel/Gnumeric for errorType=NA.

This error needs to be supported. This is part of the requirements that we must meet on the basis of Evaluator

Evaluators shall support the Error named #N/A. Evaluators may support other Errors.

Error N/A means not available.

Case 1

A comparison of two cells with ErrorType.NA should always return ErrorType.NA
When ErrorType.NAis on left side of operation, it should always return ErrorType.NA

A1 = '#N/A'
A2 ='#N/A' or any other

Steps to reproduce

const data =
['=A1=B1', '=A1>B1', '=A1<B1', '=A1>=B1', '=A1<=B1', '=A1<>B1', '=A1+B1', '=A1-B1', '=A1*B1', '=A1/B1', '=A1^B1', '=A1&B1', '=+A1', '=-A1', '=A1%'];

function createEngine(data: any[][]) {
const engine = HyperFormula.buildFromArray(data);

return {
getCellValue(cellAddress: string) {
return engine.getCellValue(adr(cellAddress));
}
}
};
it('error #N/A! with every combination should be supported by all comparison operators', () => {
        const engine = createEngine([
            ['#N/A', '#N/A', ...data],

        ]);

        expect(engine.getCellValue('C1')).toEqual(new CellError(ErrorType.NA));  // EQUAL
        expect(engine.getCellValue('D1')).toEqual(new CellError(ErrorType.NA)); // GT
        expect(engine.getCellValue('E1')).toEqual(new CellError(ErrorType.NA)); // LT
        expect(engine.getCellValue('F1')).toEqual(new CellError(ErrorType.NA)); // GTE
        expect(engine.getCellValue('G1')).toEqual(new CellError(ErrorType.NA)); // LTE 
        expect(engine.getCellValue('H1')).toEqual(new CellError(ErrorType.NA)); // NOT EQUAL
        expect(engine.getCellValue('I1')).toEqual(new CellError(ErrorType.NA)); //ADD
        expect(engine.getCellValue('J1')).toEqual(new CellError(ErrorType.NA)); //SUB
        expect(engine.getCellValue('K1')).toEqual(new CellError(ErrorType.NA)); //MULT
        expect(engine.getCellValue('L1')).toEqual(new CellError(ErrorType.NA)); // DIV
        expect(engine.getCellValue('M1')).toEqual(new CellError(ErrorType.NA)); // EXP
        expect(engine.getCellValue('N1')).toEqual(new CellError(ErrorType.NA)); // CONCAT
        expect(engine.getCellValue('O1')).toEqual(new CellError(ErrorType.NA)); // UNARY PLUS
        expect(engine.getCellValue('P1')).toEqual(new CellError(ErrorType.NA)); // UNARY MINUS
        expect(engine.getCellValue('Q1')).toEqual(new CellError(ErrorType.NA)); // PERCENTAGE

    });

Invalid results are in bold.

Name	Operator	Expected	Result
EQUAL	A1=A2	{ type: 'NA' }	{ type: 'NA' }
GT	A1>A2	{ type: 'NA' }	{ type: 'VALUE' }
LT	A1<A2	{ type: 'NA' }	{ type: 'VALUE' }
GTE	A1>=A2	{ type: 'NA' }	{ type: 'VALUE' }
LTE	A1<=A2	{ type: 'NA' }	{ type: 'VALUE' }
NOT EQUAL	A1<>A2	{ type: 'NA' }	{ type: 'NA' }
ADD	A1+A2	{ type: 'NA' }	{ type: 'NA' }
SUB	A1-A2	{ type: 'NA' }	{ type: 'NA' }
MULT	A1*A2	{ type: 'NA' }	{ type: 'NA' }
DIV	A1/A2	{ type: 'NA' }	{ type: 'NA' }
EXP	A1^A2	{ type: 'NA' }	{ type: 'NA' }
CONCAT	A1&A2	{ type: 'NA' }	{ type: 'NA' }
UNARY +	+A1	{ type: 'NA' }	{ type: 'NA' }
UNARY -	-A1	{ type: 'NA' }	{ type: 'NA' }
PERC	A1%	{ type: 'NA' }	{ type: 'NA' }

Links

https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/operator.xls

https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1017966_715980110

[aninde]

Connected to #145. ErrorType.NA is is a basic requirement, so I set up a separate issue for it.

[izulin]

I am unable to reproduce this issue, probably some other PR fixed it. Please see #185

[wojciechczerniak]

Thx!