SOLVED

Excel: inconsistent result when using conditional formatting

New Contributor

This use of conditional formatting statement does not work consistently:

IF(((Saskatchewan!$C15+$C15)<>0),1,0) (Format specifies a colour) for a range = $C$15:$C$379

 

The statement fills the cell of one worksheet with the specified colour, if the sum of the cells does not equal zero. (Note: When the worksheets are correct, the compared cells are equal in value, but opposite in sign).

The intent of the statement is to highlight the cells which are not equal. The problem is that sometimes the conditional formatting occurs even though the compared cells are identical in absolute value.

Does the presence of different numerical format or different fill or something else affect the evaluation of the conditional statement? (Note: I have matched the numerical format and cleared the fill to no colour, without any positive effect)

3 Replies

@SAngley 

What is the (maximum) number of significant decimal places of the cells? Let's say it is 2 (for example for dollars and cents).

Change the formula to

 

=ROUND(Saskatchewan!$C15+$C15,2)

 

(no need to use IF, nor even <>0)

 

(See Conditional Formatting works inconsistently

best response confirmed by SAngley (New Contributor)
Solution
Good Idea; very succinct expression. I believe that I've solve the inconsistencies by re-arranging the order of the conditional statements.

@SAngley  wrote:  ``I believe that I've solve the inconsistencies by re-arranging the order of the conditional statements``

 

Perhaps not.  Refer to my response at Conditional Formatting works inconsistently .