N() no longer returns 0 when the input is #N/A =N(NA()) for example returns #N/A in v3.6.0.4 According to help, N() "Returns 0 if parameter is text, FALSE or #NA." It works correctly in v3.5.5.3, and every previous version I've tried.
I can confirm this behavior. This renders a significant amount of our spreadsheets useless...
*** Bug 53293 has been marked as a duplicate of this bug. ***
It was an error that previous versions did not propagate error values and converted them to 0 instead. ODF OpenFormula does not define an exemption from the general rule to propagate errors for this function. Also Excel returns a #N/A error for =N(NA())
There is a bug, either in the software or the documentation. The documentation is: "N Returns the numeric value of the given parameter, if the parameter is a number. Returns the number 1 if the parameter is TRUE. Returns the number 0 for other parameters (text, FALSE or #NA). If an error occurs, other than #NA, the function returns the error value." Clearly, this says that N(NA()) should return 0. And previous versions of the software did this. I and (and apparently others) have been relying on this. If it is decided that is right to change the behaviour of N(), then I suggest that adequate warning of this is given, and the documentation amended to reflect this warning. Meanwhile, the software does not behave as the documentation says so there is a bug somewhere.
You cited the old documentation. The online help in 3.6.0 says " N Returns the numeric value of the given parameter. Returns 0 if parameter is text or FALSE. If an error occurs the function returns the error value. " Again, the previous behavior was wrong and did not match the OpenFormula specification or what other spreadsheet software does.
That's good to know. Nonetheless, users like myself have counted on that "error" for more than five years...so it would be nice to at least have a workaround and a warning somewhere..since after such a long period for a lot of people it was not a bug, but a feature.
And seriously this: Also Excel returns a #N/A error for =N(NA()) is a ridiculous argument.
I understand that from the point of view of a developer, the software has been fixed to adhere to its specifications, as it should have from the beginning. But think about it from the point of view of the users. We get information on functions from the help documentation, not the specifications. And the help documentation we cited, while perhaps outdated, is still online http://help.libreoffice.org/Calc/Information_Functions#N Like Nikos, I have used this function for years. This change turned half the values on the spreadsheets at my work to #N/A. Combined with macros that automatically copy columns around, all the information on the spreadsheets was lost after opening and saving with LibreOffice 3.6. If you absolutely have to make a change that could potentially destroy people's work, then you should make a huge warning about it when you release a new version. After identifying the problem, I reverted the spreadsheets to backups, rolled back to LibreOffice 3.5.5.3, and started searching for an alternative to N with no luck. Best I could find was using IF and ISNA but its nowhere as practical and simple. There used to be a simple way to turn N/A into 0, now there isn't. You've reduced functionality for the sake of compatibility and conforming to specifications. Either provide an alternative to N, or make this change to its behavior optional. Let us choose whether we want the software to behave as it has for years, or whether we want it to be compatible with other spreadsheet software. Your failure to provide a solution and your refusal to recognize that you made a mistake in handling this change have led me to shift to OpenOffice. Hopefully, when the folks at the Apache Foundation get around to fixing this "error" in the N function, they'll stop a moment to think of how it might affect users and act accordingly.
I agree with comment 8. Before N is changed, please may we have a new function that gives us the functionality of the old N.
IF(ISNA(expression),0,expression)
IF(ISNA(expression),0,expression) is not a satisfactory solution because it leads to widespread error 512s (formula overflows), which require significant re-engineering to eliminate.
IF(ISNA(expression),0,expression) I would not recommend that with massive columns of vlookups (which is my use-case) as it significantly slows down the overall performance of the spreadsheet (at least on our less powerful desktops). There are also other workarounds ([e.g. using countif (=IF(COUNTIF(A1:A100,"Searchterm"),VLOOKUP("Searchterm",A1:D100,2,FALSE),0) or using an additional column (which seems to be the solution with the least impact on performance but the least elegant one)... ...but they are, workarounds (more difficult to read for the user and less efficient). However, the real problem is backwards compatibility. Most of these files are used for long period of times... and having to change them is very time consuming. I also understand your standpoint as a developer, and I understand that standards compliance must be a high priority, as it is the best protection against any kind of vendor lock-in be it proprietary or open-source. However, I also think that for software to be a serious alternative for businesses, a certain decree of continuity is at least as important.(And I do not think that an office suite not aiming at the business market could be successful at the home market). Anyway, I guess a new function would not be such an easy thing to get...so we probably will have to learn to live with tis
*** Bug 64775 has been marked as a duplicate of this bug. ***
Note that as of release 4.0 there is the new function IFNA that can be used as IFNA(expression,0) with which expression is executed only once.