Bug 33705 - In LibreOffice Calc 3.3.0 N() function doesn't work as expected
Summary: In LibreOffice Calc 3.3.0 N() function doesn't work as expected
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Kohei Yoshida
URL:
Whiteboard: target:3.5 target:3.4.1
Keywords:
: 37927 (view as bug list)
Depends on:
Blocks:
 
Reported: 2011-01-29 16:49 UTC by Zarko Zivanov
Modified: 2011-06-08 12:13 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
ODS file for showing N() function bug (16.05 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-01-29 16:49 UTC, Zarko Zivanov
Details
Different values for the same formula (17.74 KB, image/png)
2011-01-30 07:55 UTC, Zarko Zivanov
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Zarko Zivanov 2011-01-29 16:49:16 UTC
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.
Comment 1 Rainer Bielefeld Retired 2011-01-30 02:13:30 UTC
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!
Comment 2 tester8 2011-01-30 06:03:31 UTC
NOT Reproducible with LO 3.3 RC4 Ubuntu 10.04 x86
Ubuntu 10.10 specific?
Comment 3 Zarko Zivanov 2011-01-30 07:55:09 UTC
Created attachment 42719 [details]
Different values for the same formula
Comment 4 Zarko Zivanov 2011-01-30 07:59:04 UTC
(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.
Comment 5 Rainer Bielefeld Retired 2011-01-30 08:37:06 UTC
I see "0" (or "0%") in all fields E8 ... I8 with WIN
Comment 6 Zarko Zivanov 2011-01-31 01:30:22 UTC
A collegue of mine tested 3.3.0.4 on Arch Linux, and the problem exists there. So, I guess, it's Linux specific.
Comment 7 Zarko Zivanov 2011-02-15 00:43:20 UTC
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.
Comment 8 Björn Michaelsen 2011-05-22 09:27:51 UTC
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?
Comment 9 Kohei Yoshida 2011-05-23 22:00:39 UTC
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.
Comment 10 Johnny Rosenberg 2011-06-04 12:23:53 UTC
(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).
Comment 11 vitriol 2011-06-04 14:21:51 UTC
*** Bug 37927 has been marked as a duplicate of this bug. ***
Comment 12 Kohei Yoshida 2011-06-08 12:13:09 UTC
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.