I'm comparing two columns within two pivot tables...
My conditional format is: =AND(K6<C6,K6<>C6) ... apply soft red highlight
What I get is correct highlight but I also get a highlight on an "equal" values?
Thanks for your help
I'm comparing two columns within two pivot tables...
My conditional format is: =AND(K6<C6,K6<>C6) ... apply soft red highlight
What I get is correct highlight but I also get a highlight on an "equal" values?
Thanks for your help
The second condition is redundant; if K < C, then K <> C.
The values may appear to be the same as formatted, but only unequal values will be highlighted.
Entia non sunt multiplicanda sine necessitate
Not sure why you need the K6<>C6 since by definition if K6<C6 it isn't! Surely the K6<C6 is sufficient?
However what is actually in K6 & C6? Have you tested them with =K6=C6 to see if you get a TRUE. Sometimes values that look equal aren't in fact. For instance 123 as a numeric looks exactly like the string 123 but the two are different.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
You are right... I only want highlights for cells that are lesser-than, right now I'm getting highlights for "equals" in addition to the lesser-thans?
I'm comparing two pivot tables but they should be rendering the same numeric format.
Have you actually checked that the the two numerics are the same? Test them both with =ISNUMBER()
You probably have two numbers which may look the same but due to the imprecision of floating point decimals they may be different at the extreme end of the decimal. In which case try wrapping your test in a ROUND(). e.g. Round(K6,3)<Round(C6,3)
Otherwise are you able to upload the workbook?
Richard,
Thank you. The absolute values are not equal but the rounded up values are - hence, the reason why my cells are highlighting.
I now have another question relating to the same comparison and highlight..
Can I use Conditional Formatting to highlight cells that are "less than" a comparative cell but are so by a value of at least 2? (ex. highlight 8<10 but not 8<9)
... maybe the formula would look similar to =and(k6<c6,k6-c6= value =2 or greater") ... of course the formula would not look exactly like that
Hi,
Yes, use
Formula:Please Login or Register to view this content.
Thank you Richard, works like a charm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks