https://bugs.freedesktop.org/show_bug.cgi?id=51136
--- Comment #6 from Roman Eisele <b...@eikota.de> 2012-06-22 01:35:34 PDT --- @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? -- Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug. _______________________________________________ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
