Apache OpenOffice (AOO) Bugzilla – Issue 46336
Conditional formatting considers value of zero equal to a missing value (na())
Last modified: 2013-08-07 15:12:27 UTC
When applying conditional formatting with a condition of: cell value equal to na() cells with values of zero (0) and FALSE are also formatted. To reproduce, create a spreadsheet with a mix of numeric data including zeros and cells with the formula '=na()'. Create a style "RED" with text color red. Select the entire sheet, go to Format/Conditional Formatting and enter condition 1 with the RED style and cell value equal to one of the cells containing na(). In addition to highlighting the cells displaying #N/A, cells with the value zero (or FALSE) are also highlighted.
Created attachment 24434 [details] Test file for conditional formatting problem, zero equals na()
Hi, compared to Excel I think we do it the better way. FALSE is interpreted as zero as the numeric value for =na() is. So the result of this conditional formating is IMHO Ok. Never the less I re-assign this as enhancement request to the requirements team. Frank
I disagree with fst: this behavior is NOT OK. It contradicts both the documentation and the behavior of the logical functions. According to the help documentation, NA() "Returns the error value #N/A" NOT zero. This is the (IMHO) correct behavior, and is consistent with the logical functions. See the results in rows 3 and 4 of the test spreadsheet--in particular, the test AND(0=na()) returns #N/A, not FALSE. If this is not the correct behavior, there is no way to distinguish missing values from zeros, and most mathematical and statistical functions will misbehave. I see no reason that the conditional formatting should not follow the same rules as the logical functions; if they are not identical, there is no way to reliably use conditional formatting to code the results of logical tests.
OpenOffice.org Issue Tracker - Feedback Request. The Issue you raised is currently assigned to 'Requirements' pending review, but has not been updated within the last 3 years. Please consider re-testing with one of the latest versions of OOo, as the problem(s) may have already been addressed. Either use the recent stable version: http://download.openoffice.org/index.html or consider trying the new OOo 3 BETA (still in testing): http://download.openoffice.org/3.0beta/ Please report back the outcome so this Issue may be Closed or Progressed as necessary - otherwise it may be Resolved as Invalid in the future. You may also wish to search for (and note) any duplicates of this Issue that may have advanced further by checking the Issue Tracker: http://www.openoffice.org/issues/query.cgi Many thanks, Andrew Cleaning-up and Closing old Issues as part of: ~ The Grand Bug Squash, pre v3 ~ http://marketing.openoffice.org/3.0/announcementbeta.html
I checked my test file with version 2.4, and the problem persists. I have not been able to test in 3.0 beta. However, the test is easy--open the test file "test_cond_format_bug.ods" making sure that auto-update is turned on--none of the zero or FALSE values should be color-coded, only the #N/A cells.