Search code examples
excelexcel-formulaconditional-formatting

Conditional format cell if surrounding values are not in ascending/descending order

I have a large book with one column of values:

A
Type
90
91
92
92
94
93
95
96
95

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.

Solution

  • 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)