CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
SierraOscar
SierraOscar

Reputation: 17647

Why does this excel formula return different results for conditional formatting?

Here is some demo data:

enter image description here

You can see the formula used in Column B returns TRUE for any consecutive duplicates, but when used as a CF condition on $A$1:$A$14 it returns different results?

Here is the CF setup:

enter image description here

Upvotes: 2

Views: 1040

Answers (2)

Axel Richter
Axel Richter

Reputation: 61945

So the condition shall be true if either the predecessor or the successor is the same value.

Within conditional formatting you can think about the row numbers as circular. So the predecessor of A1 is A1048576 and also the successor of A1048576 is A1.

The same is with the column numbers. After XFD follows A.

So your formula for the conditional formatting is:

=OR(A1048576=A1,A1=A2)

or 

=AND(A1<>"",OR(A1048576=A1,A1=A2))

for excluding empty cells.

As sheet formula this will not work. There 

=OR(IFERROR(INDIRECT("A"&ROW(A1)-1)=A1,FALSE),A1=A2)

is need.

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 37125

This may also help you. It will work for whole column also.

=IF(A1="","",OR(A1=A2,A1=IFERROR(OFFSET(A1,-1,0),"")))

enter image description here

Upvotes: 2

Related Questions