I tried to migrate from OpenOffice.Org 3.3 to LibreOffice 3.4(?) (latest current). I opened a spreadsheet created months ago in OOo that has worked fine. In LO two summing total columns began to give a "#VALUE" at row 103. I tried copying the functions from row 102 to fix the error, but it did not fix the problem. Also, the borders within the spreadsheet were much (3x) wider than in OOo 3.3. I tried to fix one of those to make it match the formatting of the old application. Nothing seemed to change the border width. I decided I did not want to have to rebuild this tool (over 1000 rows/10 columns). So I down loaded Apache OpenOffice 3.4. All of the problems with the spreadsheet in question went away. Sorry, folks. I will give you a try after your next revision. SN Mac OS X 10.6.8 (10K549)
Not sure how to state "Importance". It is a show stopper for me because it broke a working spreadsheet. Perhaps the problem would go away if I rebuilt the spreadsheet from blank in LO. I prefer not to spend my time doing that.
Thank you for your bug report! However, I don't know how we should reproduce the problem without the spreadsheet file in question (and as long as we can't reproduce it, we also can not fix it). Therefore, could you please attach the problematic spreadsheet file to this bug report? This would be very helpful, thank you very much in advance!
Created attachment 63314 [details] OOo spreadsheet
(In reply to comment #3) > Created attachment 63314 [details] > OOo spreadsheet Thank you very much for the sample file! I will give it a try as soon as I find some time ...
(In reply to comment #0) > I decided I did not want to have to rebuild this tool (over 1000 rows/10 > columns). You don't have to rebuild the tool. The problems occur where an operation is performed on a cell that is NULL. This is evaluated as a string, thus any mathematical operations involving a NULL string will fail and return an error. If you type in the number '0', as you have done so already in certain places in your sheet, then the formulae will evaluate correctly. Also, although, I didn't try or test, it might just work if you format all of your cells as Numbers, since I guess the default for them is now Text. I remember reading about the rationale behind the change in this behaviour (which was deliberate, I seem to recall), but don't recall what it was exactly, something to do with printing sheets that had null values showing up as zeroes or something like that. So, for me, this is not necessarily a bug, but a new feature. However, I can understand that it loses compatibility with the default behaviour of AOO and all previously made OOo Calc documents. Alex
@Alex: thank you for your helpful explanation! (In reply to comment #5) > The problems occur where an operation is performed on a cell that is NULL. > This is evaluated as a string, thus any mathematical operations involving > a NULL string will fail and return an error. > > If you type in the number '0', as you have done so already in certain places in > your sheet, then the formulae will evaluate correctly. Maybe it is even easier (and the problem even more simple). If I take a look at, e.g., the cells G103 and following and H103 and following which all just display "#VALUE", I see that they contain simple formulas like (G103) "=G102-C103+E103". Now for G103 the problem are C103 and/or E103, which (if I understand correctly!) evaluate to 0, but are treated as NULL/text values now. To fix this, you can either put "0" into C103, as Alex suggested, or, even simpler, just *delete* the contents of C103 -- because C103 is NOT empty, as it looks, but contains a single " " (space). As soon as I delete the space in C103, LibreOffice (3.5.4) correctly recognizes the contents of C103 as number = 0.00, and G103 and following contain numeric values again instead of "#VALUE". Exactly the same is true for D103: as soon as I delete the " " from D103, the colum H103 and following contain numeric values again instead of "#VALUE". So, if I understand correctly, this problem is even an arbitrarily change of behaviour. IMHO it makes sense that LibreOffice, when it encounters a cell containing a " ", thinks that this cell contains plain text (not a number) -- of course, " " *is* text and not a number. It does make some sense to evaluate a really empty cell to 0.0, but it is at least not self-explaining that " " should be evaluated to 0.0, too ... Therefore, the change of the handling of cells containing " " in LibreOffice 3.4 seems reasonable to me. It is still a pity that this breaks existing spreadsheets which contain some " " that are meant to evaluate to 0.0 . But as far as I can see, just deleting some spaces from a relatively small number of cells (e.g., E155 is another candidate) will suffice to make your sheet working again -- it is not even necessary to put zeros into all that cells. A single "Find and replace" action which "Regular expression" checked and "^ $" as value in the "Search for" and just "" (empty) in the "Replace" field may be helpful here. It may be necessary to select "Values" from the popup menu "Search in". If I just insert these values and then click "Replace all" once, all "#VALUE" entries seem to be gone -- I see numeric values everywhere. Maybe this is sufficient to heal your complete spreadsheet?
Created attachment 63338 [details] Sample document, fixed (I hope) via replace all "^ $" by "" (RegEx) @ sjn45@yahoo.com: Please take a look at the attachment. It is your sample document, improved by the simple Regular Expressions (RegEx) Find-and-Replace action I have suggested in my previous comment. Could you please examine the file and state if the problem is really fixed, i.e. if all formulas seem work correctly again? Thank you very much!
(In reply to comment #6) > So, if I understand correctly, this problem is even an arbitrarily > change of behaviour. Oops -- typo! I mean, "this problem is NOT even an arbitrary change". > A single "Find and replace" action which "Regular expression" checked ... Please read "with" instead of "which". I was too hasty, sorry! * Changed Status to NOTABUG, because of comment #5 (and comment #6).
Created attachment 63443 [details] Screenshot with comments: "Find and Replace" settings A little help: the screenshot shows (with comments in red) which settings in the "Find and Replace" dialog window of LibreOffice Calc are necessary to remove the contents of each cell that contains just one or more spaces (blanks). A single "Replace All" operation with these settings seems sufficient to make the sample file work again.
Side note: original problem resolved with bug 50345
Thank you, folks for you help. I still think you have created a problem for yourselves by allowing invisible elements (space) to break a formula. If the cause of the problem is unseen by the user, we do not know what to change to fix the problem. I would prefer the formulas ignore invisible elements. Thanks again. SJN