I have a large book with one column of values:

Where I want to colour the ones that are not in ascending/descending successive order.

In this case I want to colour the 94, 93 and the 96 after.

The formula I tried:

=OR(AND($A2 < $A1; $A2 < $A3);AND($A2 > $A1; $A2 > $A3))

But this doesn't work. I don't really see what I'm doing wrong, and maybe there is another simpler way of solving this.

Thanks.

CodePudding user response：

You're on the right track, but I believe there's something wrong in your formula: instead of:

=OR(AND($A2 < $A1; $A2 < $A3);AND($A2 > $A1; $A2 > $A3))

Try this:

=OR(AND($A1 < $A2; $A2 < $A3);AND($A1 > $A2; $A2 > $A3))
          ^     ^                   ^     ^
          ^     ^                   ^     ^

(The ^ clarify where you went wrong)

CodePudding user response：

You can use this formula within your format condition:

=OR((A2-1)=A1,(A2=A1),(N(A1)=0))=FALSE