Forum Discussion
Conditional formatting formula not recognizing equals
Working in Excel Table "Alignment"
I'm comparing the value of two cells, each of which contains a lookup.
e.g. =VLOOKUP([@[PFD Seq]],OrigPFD,13,TRUE)
The lookups values were calculated independently, but rounded to 2 decimal places to avoid precision issues, so 1.496 and 1.501 should both be "equal".
The values show as identical, and if I use the formula
=IF(Alignment[@[PFD Area]]=Alignment[@[Working Area]],1,0)
the values ARE seen as being equal - but the conditional formatting statement
"Cell Value <>$G2" applied to =$H$H
doesn't see them as such. Well, conditional formatting sees some, but not all of them - apparently all of the same value in sequence except the last... which I think is a clue, but don't get it.
I must be missing something in the conditional formatting, but can't see it. Can you?
- Make sure you use the ROUND function on both sides of the (ub)equality test
I actually adjusted the workbook to put the rounding function on the data source instead of the lookup function. Still no luck.
BUT! I changed the the condition to formula and set it as =C$1=D$1 and voila. Perfect.
Why the prefab comparison doesn't work is beyond me...
- What is important is to write the formula with in mind that you are in fact editing the formula for the top-left-most cell of the range which contains the CF rule. And that you must pay attention to using absolute and relative references in the right way.
