Bug 37860 - Formula returns #VALUE in 3.4RC2 but works as expected in 3.3
Summary: Formula returns #VALUE in 3.4RC2 but works as expected in 3.3
Status: RESOLVED DUPLICATE of bug 44720
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.0 release
Hardware: x86 (IA32) Windows (All)
: medium enhancement
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords:
: 37891 39837 40753 40968 41746 42575 42756 (view as bug list)
Depends on: 44720
Blocks: mab3.4
  Show dependency treegraph
 
Reported: 2011-06-02 09:55 UTC by Bruce Leary
Modified: 2012-10-04 09:38 UTC (History)
15 users (show)

See Also:
Crash report or crash signature:


Attachments
Simple spreadsheet to illustrate the problem. Cells A1 to C1. (7.88 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-06-02 09:55 UTC, Bruce Leary
Details
Sample Document, see Comment 6 (17.49 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-06-06 09:01 UTC, Rainer Bielefeld Retired
Details
When Cond=>NotBlank use IF(Cond before using Blank/NonBlank value (17.82 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-06-08 01:32 UTC, LeMoyne Castle
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Bruce Leary 2011-06-02 09:55:36 UTC
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.
Comment 1 noname 2011-06-02 12:42:55 UTC
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]
Comment 2 Oliver Brinzing 2011-06-03 07:34:32 UTC
.
Comment 3 vitriol 2011-06-05 01:47:01 UTC
it's a regression against 3.3.2
Comment 4 Andras Timar 2011-06-06 03:50:20 UTC
Kohei, can you take a look?
Comment 5 Kohei Yoshida 2011-06-06 07:57:10 UTC
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.
Comment 6 Rainer Bielefeld Retired 2011-06-06 08:59:42 UTC
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.
Comment 7 Rainer Bielefeld Retired 2011-06-06 09:01:42 UTC
Created attachment 47615 [details]
Sample Document, see Comment 6
Comment 8 Kohei Yoshida 2011-06-06 09:06:12 UTC
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.
Comment 9 Oliver Brinzing 2011-06-06 09:44:35 UTC
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" ...
Comment 10 LeMoyne Castle 2011-06-08 01:32:25 UTC
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
Comment 11 Andras Timar 2011-06-09 02:43:47 UTC
*** Bug 37891 has been marked as a duplicate of this bug. ***
Comment 12 Rainer Bielefeld Retired 2011-06-10 02:58:04 UTC
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.
Comment 13 Rainer Bielefeld Retired 2011-09-10 09:32:42 UTC
*** Bug 40753 has been marked as a duplicate of this bug. ***
Comment 14 vitriol 2011-09-17 08:23:51 UTC
*** Bug 40968 has been marked as a duplicate of this bug. ***
Comment 15 James Fuqua 2011-09-19 04:07:42 UTC
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.
Comment 16 Kohei Yoshida 2011-09-19 06:28:21 UTC
(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.
Comment 17 BrianH 2011-09-19 06:37:10 UTC
(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.
Comment 18 Kohei Yoshida 2011-09-19 06:49:42 UTC
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.
Comment 19 Kohei Yoshida 2011-09-19 06:52:56 UTC
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!
Comment 20 BrianH 2011-09-19 11:27:20 UTC
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.
Comment 21 James Fuqua 2011-10-06 12:26:06 UTC
(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.
Comment 22 Josh Leverette 2011-10-07 20:01:09 UTC
(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.
Comment 23 vitriol 2011-10-15 02:46:49 UTC
*** Bug 41746 has been marked as a duplicate of this bug. ***
Comment 24 vitriol 2011-11-03 23:11:28 UTC
*** Bug 42575 has been marked as a duplicate of this bug. ***
Comment 25 Björn Michaelsen 2011-11-15 15:43:44 UTC
adding Kohei as 'easyhackifier' to cc.

Could you try to help Josh getting started, if he is still interested?
Comment 26 Kohei Yoshida 2011-11-16 07:22:38 UTC
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.
Comment 27 Michael Meeks 2012-01-12 06:16:01 UTC
> 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 :-)
Comment 28 Michael Meeks 2012-01-12 06:21:11 UTC
un-marking as an easy hack there is a cleaner description in bug #44720
Comment 29 Kohei Yoshida 2012-01-12 11:13:34 UTC
Removing from most annoying as it's not qualified.
Comment 30 jkonecny 2012-01-25 11:33:51 UTC
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.
Comment 31 James Fuqua 2012-03-14 11:22:09 UTC
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 "".
Comment 32 Philipp Gassmann 2012-03-20 03:54:58 UTC
(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.
Comment 33 Philipp Gassmann 2012-03-20 03:56:53 UTC
Wrong Bug number, should've been Bug 39837
Comment 34 Philipp Gassmann 2012-03-20 03:59:38 UTC
*** Bug 39837 has been marked as a duplicate of this bug. ***
Comment 35 Kohei Yoshida 2012-03-20 06:42:41 UTC

*** This bug has been marked as a duplicate of bug 44720 ***
Comment 36 James Fuqua 2012-03-20 07:50:25 UTC
@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
Comment 37 GerardF 2012-10-04 09:38:08 UTC
*** Bug 42756 has been marked as a duplicate of this bug. ***