bug #37860# has some complaints about changes to behaviour of some corner cases in calculation to make us more interoperable. It would be good to add some per-document settings that are tweak-able to allow these things to be altered. I snip Kohei's description: [snip] The goal: introduce a configuration option to toggle this behavior in an user-friendly way. One idea is to use the Formula options page to add such option. But consult with the UX people on the libreoffice-ux-advise@lists.freedesktop.org to get their opinion on how such configuration option should be introduced. ... Also, there are other conflicting formula behaviors between OOo and Excel, so it's probably best to make such option extensible to make it easier to add more configuration options in the future. ... Code pointer: Formula interpreter: * sc is the module where all the Calc code is kept, including the formula interpreter code (look for class ScInterpreter). * ScInpterpreter::GetCellValueOrZero() is the method that gets called to retrieve the value of another cell. This method is relevant for this easy hack. Configuration options: * This configuration option should be stored with the document, so look for ScDocOptions class to add a new document configuration option (also in sc). It has one child class ScDocCfg, which handles loading and storing of the options from and to the user configuration directory. Add the new option to the aFormulaItem member. * Now, every user configuration option needs to be defined in the officecfg module. Look for officecfg/registry/schema/org/openoffice/Office/Calc.xcs. The new option should be under the "Formula" group. UI: * First, you need ask on libreoffice-ux-advise@lists.freedesktop.org about how best to add this new configuration option in the UI - no subscription is required. * Tab pages for Calc related options pages are all located in sc/source/ui/optdlg. I hope this is enough pointer to get started.
Please do not add non-development related comments to this bug that are not based on the coding interactions :-)
*** Bug 37860 has been marked as a duplicate of this bug. ***
Removed EASYHACK from summary
I'll look into this for 3.7.
No longer an easy hack.
Created attachment 64581 [details] new configuration option to toggle this behavior.
This is now implemented on master (for 3.7).
*** Bug 50345 has been marked as a duplicate of this bug. ***
@Kohei Yoshida: Can you please tell me where the configuration option shown in screenshot can be found?
It seems that most spreadsheet sw is missing a proper way to condtionally set an empty cell. Why not create an empty() function to be used like: if(cond,result,empty()) I guess traditionally we use if(cond,result,"") to do this today, but the use of an empty string as empty cell has unexplored consequences. Of course any function with empty() as argument, should return as if the argument cell was deleted/untouched.
(In reply to comment #9) > @Kohei Yoshida: > Can you please tell me where the configuration option shown in screenshot > can be found? It's in Tools-Options-Calc-Formula, and set the detailed calculation settings to 'Custom', then click on Details (or similar). I may not be totally accurate as I don't have a build I can use at the moment...
FYI, the OpenFormula specification already has ISBLANK(), which tells you if a given cell is blank or not (and empty strings do NOT count as a blank). LibreOffice 3.5.6.2 (at least) already implements ISBLANK(), too. So I think what some commenters wanted is already available.
(In reply to comment #12) > FYI, the OpenFormula specification already has ISBLANK(), which tells you if > a given cell is blank or not (and empty strings do NOT count as a blank). > LibreOffice 3.5.6.2 (at least) already implements ISBLANK(), too. So I > think what some commenters wanted is already available. I'm aware of that, but that is not what I asked for. My request is a function to return the equivalent of an empty cell. Now we are forced to use "" to set [conditional] empty cell and that causes issues, because its not really an empty cell, but an empty string.