Created attachment 42705 [details] ODS file for showing N() function bug This bug existed in Ubuntu's 3.2.1 version of OpenOffice, and I filled a bug report for it, but it was never solved. Oracle's version of OpenOffice 3.2.1 (from www.openoffice.org, OOO320m18 build 9502) does not have this bug. In 32-bit Ubuntu 10.10, in LibreOffice Calc installed from PPA (OOO330m19, Ubuntu package 1:3.3.0-1maverick1) the N() function doesn't work as expected. The function should return "0" for non-numeric input (from help file: "Returns the numeric value of the given parameter. Returns 0 if parameter is text, FALSE or #NA."), but sometimes returns "#VALUE!", sometimes "###", depending on which cell I put it on, which denies its purpose (and makes a lot of my documents completly unusable). In test example I provided as attachment (TestN.ods), you could see (I hope) that same formula "=N(O4)" sometimes gives "#VALUE!" sometimes "###", and it should ALWAYS be "0" for text or empty cells. I had problems installing RC3 from PPA, and I waited for the final version. That's the reason that I'm filling this a bit late.
NOT Reproducible with "LibreOffice 3.3.0 RC4 - WIN7 Home Premium (64bit) German UI [OOO330m19 (build 6 / tag 3.3.0.4)]", I always see "0" in 'E8' of sample document. Linux-specific? Related to <http://www.openoffice.org/issues/show_bug.cgi?id=109861>? @Zarko Zivanov: What does "sometimes" mean in your report? Only when you open document on Christmas Eve or 2 of 5 when you press <f9>? ;-) Please contribute a link to your OOo report!
NOT Reproducible with LO 3.3 RC4 Ubuntu 10.04 x86 Ubuntu 10.10 specific?
Created attachment 42719 [details] Different values for the same formula
(In reply to comment #1) > sample document. Linux-specific? > Related to > <http://www.openoffice.org/issues/show_bug.cgi?id=109861>? It looks related... I'm not sure if this is Ubuntu specific (OOo on 10.04 worked well), I'll try on WinXP tomorrow. > What does "sometimes" mean in your report? Only when you open document on > Christmas Eve or 2 of 5 when you press <f9>? ;-) I added a screenshot for better clarification. Fields E8 to I8 have the same formula "=N(O4)", but different output.
I see "0" (or "0%") in all fields E8 ... I8 with WIN
A collegue of mine tested 3.3.0.4 on Arch Linux, and the problem exists there. So, I guess, it's Linux specific.
I also tested new OpenOffice 3.3.0 from www.openoffice.org (OOo330m20), and in it's version of Calc, N() function works as expected.
Comfirmed on libreoffice-3.3.2-1ubuntu5 and on current master without any vendor patches. According to: http://openoffice.org/bugzilla/show_bug.cgi?id=109861 it was fixed between dev300m77 and dev300m78. @kohei: Could you have a look?
We do text handling differently than OOo, so we need to fix this our own way. FYI the code we have in ScInterpreter::ScN() is identical to the code they have in their latest dev 3.4 release, but the results are different.
(In reply to comment #0) > but sometimes returns "#VALUE!", sometimes "###", depending on which > cell I put it on That seems to be because some cells are not wide enough to show ”#VALUE!”. I made them wider and there it was, ”#VALUE!”. LibreOffice 3.4, Ubuntu 10.10. I downloaded LibreOffice from the LibreOffice site and installed it with gnome-terminal (”sudo dpkg -i *deb” and so on).
*** Bug 37927 has been marked as a duplicate of this bug. ***
Fixed on master (for 3.5) http://cgit.freedesktop.org/libreoffice/calc/commit/?id=5743e7b36220e9689091812cef7a4396dd48c4e6 Request for review sent to the mailing list for possible inclusion into 3.4.1.