if returns empty string instead 0

[doczoidberg]

following formula should result to 0 but results in an empty string

this is important because e.g. the formula can be encapsulated by another one e.g. VLOOKUP or in another IF statement

[krzysztofspilka]

@izulin do we have any update on this?

[bardek8]

We started discussing this offline. Actually this case is quite complicated as this is the place of inconsistency between Excel, Google Sheets and LibreOffice Calc.
With the current implementation of HyperFormula we cannot behave as in Excel - this will require storing auxiliary data with each cell value and processing it appropriately. Currently we behave as in Google Sheets.

See the difference between Google Sheets:

and Excel:

[doczoidberg]

An adaption of the if function for excel should be enough? If undefined==0 results to true and returns "0" if a cell reference is blank for excel tables all cases should be right? No need for auxilary data? Only a global setting for the calculation type and an adapted if function which handles the different calcultation types.

Your plans are not to support Excel soon?

[AMBudnik]

Thank you for doing the investigation @bardek8. I've spoken with @krzysztofspilka and we both agree that we should consider switching to Excel's approach but as it is not an easy process we should mark this topic as a feature request and get back to it after doing the full investigation.

@doczoidberg we'll update you on every change to this topic.

[doczoidberg]

@AMBudnik can you give a rough time estimation?

[AMBudnik]

@doczoidberg some of our crucial team members are ooo, we cannot start the process without them. As soon as they meet and agree on the next step towards the goal this topic will get updated accordingly.

[doczoidberg]

any news here?

[bardek8]

Sorry for the late reply. Unfortunately, currently we cannot provide any precise time estimations.
This is an involved issue that requires deep understanding of the Excel's behavior, which is very different from GoogleSheets and LibreOffice. We envisage that this will be a big change in HF, so we decided to address it in the later future, after we finish the project of adding new functions to HF.

[doczoidberg]

I think you are overcomplating things here. Plese rethink about my comment:

If undefined==0 results to true and returns "0" if a cell reference is blank for excel tables all cases should be right? No need for auxilary data? Only a global setting for the calculation type and an adapted if function which handles the different calcultation types.

Can you give me hints where I have to edit the code? I can make a PR then

See also #488

[izulin]

Thanks! As @bardek8 mentioned in #496, we will investigate it soon and hopefully this will not be that bad as we thought first.
Here are the relevant places of code to start working with IF:

https://github.com/handsontable/hyperformula/blob/develop/src/interpreter/plugin/BooleanPlugin.ts#L24

https://github.com/handsontable/hyperformula/blob/develop/src/interpreter/plugin/FunctionPlugin.ts#L223

https://github.com/handsontable/hyperformula/blob/develop/src/interpreter/ArithmeticHelper.ts#L292

And here we process the second argument of IF:

https://github.com/handsontable/hyperformula/blob/develop/src/interpreter/plugin/FunctionPlugin.ts#L225

[wojciechczerniak]

Done in #500. Implemented as a Vendor Behaviour setting evaluateNullToZero.

hyperformula/src/Config.ts

Lines 201 to 209 in 4e092bd

	/**
	* Sets the compatibility mode for behaviour of null value.
	* If set, formula evaluating to null evaluates to 0 instead.
	*
	* @default false
	*
	* @category Engine
	*/
	evaluateNullToZero: boolean,