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)
Forum Discussion
Solved
Excel: inconsistent result when using conditional formatting
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)
- Good Idea; very succinct expression. I believe that I've solve the inconsistencies by re-arranging the order of the conditional statements.
3 Replies
- 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 .
