[Bug]: Functions do not take null named expressions into account

[jboysen]

Description

We have found a deviation from Excel where named expressions not set are taken into account in functions. E.g. for the following code:

hf.addNamedExpression("a", "=1");
hf.addNamedExpression("b", "=1");
hf.addNamedExpression("d", null);
hf.addNamedExpression("e", "=AVERAGE(a,b,c)");
// e yields 0.666666666666667, but should yield 1

In Excel the corresponding example correctly yields 1: Hyperformula function issue.xlsx

This causes some issues, as it either requires that users of Hyperformula always define all required named expressions or they take this into account in formulas, which will give very long formulas.

Video or screenshots

No response

Demo

https://jsfiddle.net/9muhqwct/3/

HyperFormula version

2.6.2

Your framework

No response

Your environment

Chrome 122 on Windows 11

[sequba]

Hi @jboysen! Thank you for reaching out to us. This behavior doesn't seem correct. We will fix it in one of the upcoming releases.

It seems that there is an issue with the implementation of the function AVERAGE. The non-numeric arguments passed as cell references or raw values are ignored (following the definition of AVERAGE). Named expression arguments, however, are coerced to number instead.

Other functions from NumericAggregationPlugin may also be affected by this issue.

[jboysen]

Hi @sequba, Thanks a lot, sounds nice. Looking forward to the fix.