Created attachment 47459 [details] Simple spreadsheet to illustrate the problem. Cells A1 to C1. A sample ODS is attached to illustrate the problem. A formula testing another cell that contains an =IF that resolves to "" returns #VALUE instead of the correct result. Works as expected in 3.3. Fails in 3.4RC2.
Over here, it only gives !VALUE# the first time the file is imported. When giving a number in B1 and clearing it, the result is 'BLANK'. Looks like the cel is uninitialized the first time and filled when you give a value. BTW, setting B1 to zero (0) also gives you 'BLANK', which is not actually true. I got the same results above under Win2K with LO 3.4 RC2 [LibreOffice 3.4.0 OOO340m1 (Build:12)] and GO-OO [OpenOffice.org 3.2.1 OOO320m19 (Build:9505) ooo-build 2010-07-18]
.
it's a regression against 3.3.2
Kohei, can you take a look?
This is by design; a blank cell implicitly has a value of 0, but a formula cell whose result is a null string does not have a value of 0, and if such cell is referenced and is expected to have a numeric value, #VALUE! returns. On that note, the behavior in 3.3.2 is a bug (if that's indeed how it behaves in that version). I'm actually very surprised that 3.3.2 behaves that way. I should never have. This behavior was necessary in order to maintain VBA compatibility with Excel documents. FWIW this behavior differs from OOo's original behavior, which is probably where the confusion comes from.
I strictly disagree with the current behavior as long as nobody can tell me a simple solution for attached "AnOtherSample.ods". I need empty cells, not a value "0" in clumn H. Where there's a will there's a way, but I think the solution in Sheet "WhiteCharSolution" is far-fetched.
Created attachment 47615 [details] Sample Document, see Comment 6
Disagree or not, we need a consistent behavior that works in diverse situations, which include interoperability needs. This is one of those corner case situations where the behavior was not really defined but some users started to depend on it.
i do not like the new behaviour too, cause it will break compatibility and will force users to spend a lot of time to fix existing spreadsheets. maybe you can use =IF(F2>0;SUM(E2;H2)*0,02;"") in cell J2 in your example. this is a similar solution as it was necessary when oo 3.2 started to "calculate with strings" ...
Created attachment 47706 [details] When Cond=>NotBlank use IF(Cond before using Blank/NonBlank value When a Condition makes a cell either a number or blank, re-use the condition as a test before using the value that can be either Blank or Numeric. May (i.e. will) require alteration of existing spreadsheets and could get ugly with complex conditions on whether the cell is Blank or Numeric
*** Bug 37891 has been marked as a duplicate of this bug. ***
RC2 is bit by bit identical with release version, so separate items in the version picker are useless. Changes have been discussed with Michael Meeks.
*** Bug 40753 has been marked as a duplicate of this bug. ***
*** Bug 40968 has been marked as a duplicate of this bug. ***
Would like to know why this bug was closed. ALL, and I mean all, of my spreadsheets have some form of a function that includes blank "" cells. And all of them no longer work. I understand the workaround or "elegant" solution, but the plain fact is that you can no longer import Excel or OO spreadsheets that use such functions. I personally don't have time to go back and rewrite all of my spreadsheets. This is a very real bug with very real consequences for usability.
(In reply to comment #15) > but the plain fact is that you can no longer import Excel or OO spreadsheets > that use such functions. BTW Excel behaves this way. OOo didn't. So it's not so plain fact, is it.
(In reply to comment #16) > (In reply to comment #15) > > but the plain fact is that you can no longer import Excel or OO spreadsheets > > that use such functions. > BTW Excel behaves this way. OOo didn't. So it's not so plain fact, is it. For me it is clear. Either LO wants to have backwards compatibility or it wants some sort of 'purist' approach. The reason I chose OO in the first place was it's almost global compatibility. Now LO is incompatible with OO and with PREVIOUS versions of LO! I now have some machines on LO 3.3 and others still on OO 3.2. All working nicely. I have regresed all machines which went to 3.4 Like others there is no way I will choose to 'upgrade' to an incompatible product.
Alright. I'll turn this into an EasyHack. 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. Good luck, and have fun!
Sounds like a good idea... Some ideas on behaviour... The default 'option' for a spreadsheet should be taken from the source. 1. an OO or LO 3.3 spreadsheet should work as now i.e. blanks are treated as zero. This maintains the important aspect of backwards compatibility. 2. An excel spreadsheet would open with whatever options were applicable to it, such as separators etc. 3. LO 3.4 and going forward should indicate the 'version' to allow (1) above to work; assuming it has to behave differently to 3.3 My preference would be to use an alternate file type e.g '.los' to make it very visible and to avoid the M$ problems you get with their .doc standards having a 'hidden' version number in the source. 4. The ability to 'convert' from one standard to another should be available or automated as part of the save to filetype dialog.
(In reply to comment #16) > (In reply to comment #15) > > > but the plain fact is that you can no longer import Excel or OO spreadsheets > > that use such functions. > > BTW Excel behaves this way. OOo didn't. So it's not so plain fact, is it. Sorry to be so late replying.... to the snippy comment. However, I want to understand where you are coming from. This is a _prior_ bug that has existed... since I've been working on spreadsheets (some 20 years, now)? Just so you understand where _I_ am coming from. 1) I base my comments on Excel 98... it has been THAT long since I have used it. I have used OpenOffice almost from the beginning. I have used LibreOffice since the first day it went public. 2) Some of my spreadsheets are 10-15 years old (or at least parts of them are). I have been using some version of the formula: A1=if(x<1;"";x+y); A2=1; A3=A1+A2 for that entire time. It has always worked. It worked in LO 3.3. It worked in LO 1.0. 3) I, as a user, have significant amounts of man-hours invested in my spreadsheets. When you say, "Oh, that was a prior bug that we're just now getting around to fixing", you are in essence saying, "Not my problem, user error." As a user, I expect thing to no be broken on the coding side (elegant or no). 4) I have been an evangelist for the OpenOffice, then LibreOffice, suite and have convinced many individual users and a few offices to convert; however, I must rethink this if "correct" or "elegant" coding approaches are going to so cavalierly going to trump users needs. 5) The toggle option does not sound like a sound approach. If anything, there should be a toggle to turn on the current default, not to turn on what has been the default for more than a decade.
(In reply to comment #18) > Alright. I'll turn this into an EasyHack. > > 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. I've done programming for years, but I've never contributed to an existing project, and I managed to compile 'the beast' from scratch, so I now have a fully functional copy of LibreOffice running from the latest git branch, and I was looking for tasks I could do and found this. However, I was wondering if I could have some advice as to which files I need to read through to get this change completed? There is a lot of source code, and I'm not very familiar with it, so I don't know where to begin.. and couldn't find a guide.
*** Bug 41746 has been marked as a duplicate of this bug. ***
*** Bug 42575 has been marked as a duplicate of this bug. ***
adding Kohei as 'easyhackifier' to cc. Could you try to help Josh getting started, if he is still interested?
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 to subscribe and ask on libreoffice-ux-advise@lists.freedesktop.org about how best to add this new configuration option in the UI. * Tab pages for Calc related options pages are all located in sc/source/ui/optdlg. I hope this is enough pointer to get started.
> 5) The toggle option does not sound like a sound approach. If anything, > there should be a toggle to turn on the current default, not to turn on > what has been the default for more than a decade. That's no problem; we can add a toggle option that is a per-document setting, and is set to 'on' for older documents that we import :-) Of course - what is required is for someone to work on the easy hack; IMHO it'd be better to separate that work from this bug - since this bug is (by now) long and tangled, with good and more mixed ideas in it: eg. the idea of new file extensions is a vast pain in the backside more than can be imagined, and isn't going to happen :-)
un-marking as an easy hack there is a cleaner description in bug #44720
Removing from most annoying as it's not qualified.
This appears to be a dupe of 39837. https://bugs.freedesktop.org/show_bug.cgi?id=39837 I think this bug should be closed invalid. LO now works the same way as excel. I had an OOo user using the space bar to clear out data. It all looked ok to her because (A cell with a space in it) + 1 = 1 in OOo and then she sent it as an xls file to an excel user. The excel user sees (A cell with a space in it) + 1 = #VALUE! so the excel user thinks OOo has a bug. She also sent it to a LO user. The LO user thinks there is a compatibility issue somewhere between OOo/LO/Excel. If LO works the same as excel it is much better for compatibility. I am switching my OOo users to LO to avoid this problem. Again... Please close this as invalid.
To all who worked on this bug: Thank you, thank you, thank you. All seems to work fine in 3.5.0rc3. And to those who argue this was not a bug, a formula that returns the value "" in a cell should be read as a BLANK cell, not a space. A "Space" does not equal "".
(In reply to comment #31) @James Fuqua: I cannot confirm this. Tried with 3.5.0 on Ubuntu Precise and 3.5.1 rpms on fedora, [=""] + [1] = #Value Bug 44720 and Bug 37891 are still open and unfortunately there's seemingly no work done.
Wrong Bug number, should've been Bug 39837
*** Bug 39837 has been marked as a duplicate of this bug. ***
*** This bug has been marked as a duplicate of bug 44720 ***
@Philipp Gassmann and all concerned. Please excuse my initial exuberance -- you are correct, this is still not resolved in Ver#3.5.1.2 Windows. For some reason, =sum() now works correctly (that was the function most of my sheet rely on), but most other functions do not work if one of the values is [""]. So, A1=1 A2=2 A3=if(A2<A1,A2,"") Sum(A1:A3)=5 A1+A2+A3=#VALUE
*** Bug 42756 has been marked as a duplicate of this bug. ***