Issue 46336

Summary: Conditional formatting considers value of zero equal to a missing value (na())
Product: Calc Reporter: twainwright <thomas.wainwright>
Component: formattingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P4 CC: issues
Version: OOo 2.0 Beta   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
Test file for conditional formatting problem, zero equals na() none

Description twainwright 2005-03-31 00:48:45 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.
Comment 1 twainwright 2005-03-31 00:54:18 UTC
Created attachment 24434 [details]
Test file for conditional formatting problem, zero equals na()
Comment 2 frank 2005-03-31 08:38:22 UTC

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.

Comment 3 twainwright 2005-03-31 17:23:37 UTC
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.
Comment 4 ace_dent 2008-05-16 00:53:50 UTC 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:
or consider trying the new OOo 3 BETA (still in testing):
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:
Many thanks,
Cleaning-up and Closing old Issues as part of:
~ The Grand Bug Squash, pre v3 ~
Comment 5 twainwright 2008-05-17 00:26:38 UTC
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.