Issue 113739 - Function N() is inconsistent
Summary: Function N() is inconsistent
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOO320m12
Hardware: All All
: P3 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Keywords: ms_interoperability, regression
: 113872 (view as issue list)
Depends on:
Reported: 2010-08-09 09:45 UTC by keme6206
Modified: 2017-05-20 11:41 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

test cases (8.21 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-08-18 17:58 UTC, ooo
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description keme6206 2010-08-09 09:45:38 UTC
Inconsistent behaviour of the N() function when the parameter is a string data
type with numeric content.

I assume that the intended use for this function is to explicitly allow the
entry of string values in cells that are used by formulas for numerical
calculations, to avoid/ignore certain errors.

The application help states that N will return zero with text parameters.
I tested with the following inputs:
Text constant entered in cell
A1: '4 

Formula returning text value
A2: ="4"

Nonnumeric text
A3: jhg

I tested with several OOo builds, with results as indicated below (all packages
"en-US", except as noted):

OOo 3.1.1 Novell edition, as bundled with OpenSuSE 11.2, build ID: 9319 (not
updated after installing, afaik)
N(A1)=4 ; N(A2)=4 ; N(A3)=0 ; N("4")=0

I'm aware that this is a third party build. Still included for reference.

OOo 3.2.0 Norwegian for MS Windows, running on Windows XP pro SP3 (OOO320M12)
N(A1)=4 ; N(A2)=4 ; N(A3)=0 ; N("4")=0

Didn't make a note of build ID before upgrading to 3.2.1 (for next entry). Sorry!

OOo 3.2.1 Norwegian for MS Windows, running on Windows XP pro SP3 (OOO320M18,
build 9502)
N(A1)=4 ; N(A2)=4 ; N(A3)=#VALUE ; N("4")=0

Note that here the string handling is clearly wrong. Seems to be corrected in
the dev version, though.

OOo-dev 3.3.0 for MS Windows, running on Windows XP pro SP3 (OOO330m2, build 9516)
N(A1)=4 ; N(A2)=4 ; N(A3)=0 ; N("4")=0

OOo 3.2.0 for OS-X/intel, running on OS-X 10.5.8 (OOO320m8, build 9472)
N(A1)=4 ; N(A2)=4 ; N(A3)=#VALUE ; N("4")=0

OOo 3.2.1 for OS-X/intel, running on OS-X 10.5.8 (OOO320m18, build 9502)
N(A1)=4 ; N(A2)=4 ; N(A3)=#VALUE ; N("4")=0

The only situation where N() behaves consistently as documented (per the
application's help function) is the situation where an inline constant is given.
This is also the least useful situation (I can't imagine any practical setting
where it would be useful).

With cell content, N() consistently attempts to interpret numeric content in
strings. This is not according to the application help, which states that
parameters of data type "text" will return zero. It is also inconsistent with
the behaviour of the "major player", MS Excel.

Also, with some builds, the function totally fails to do its job (as per my
assumption of intended use, above), returning an error for text that cannot be
interpreted as a number.
Comment 1 ooo 2010-08-13 21:59:22 UTC
*** Issue 113872 has been marked as a duplicate of this issue. ***
Comment 2 ooo 2010-08-13 22:02:24 UTC
In short: =N("4") returns 0, but =N(A1) with A1:'4 returns 4. Instead it should
also return 0. Excel does this. OOo3.1.1 did that as well.
Comment 3 ooo 2010-08-18 17:57:36 UTC
In cws calc58:

changeset c8bc74228dbf
M sc/source/core/tool/interpr1.cxx
M sc/source/core/tool/interpr4.cxx

You can observe the progress and possible integration date of CWS calc58 at
Comment 4 ooo 2010-08-18 17:58:23 UTC
Created attachment 71171 [details]
test cases
Comment 5 ooo 2010-08-23 11:31:41 UTC
Reassigning to QA for verification.
Comment 6 oc 2010-08-25 10:57:23 UTC
verified in internal build cws_calc58