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 184.108.40.206)]", I always see "0" in 'E8' of sample document. Linux-specific?
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
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 220.127.116.11 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.
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)
Request for review sent to the mailing list for possible inclusion into 3.4.1.