I am formatting a column in a way that the 2 lowest values are painted green, with the custom formula:

=K6<SMALL($K$6:$K$44; 3)

From my understanding this takes the third lowest value and paints the ones beneath that (the two lowest) green.
This worked fine until, today we had value 168, 169.5 169.5 and values above that. It only colored 168 as green although, 169.5 is the 2nd lowest value. Why does it not mark all 3 green?

Is there a way I can easily fix this ? So that it always marks the 2 lowest values, even if there are multiple of them.

CodePudding user response：

You asked for conditional formatting that:

...always marks the 2 lowest values, even if there are multiple of them.

Try the following formula

=OR(K6=MIN(SORTN($K$6:$K$44,2,2,1,1)),K6=MAX(SORTN($K$6:$K$44,2,2,1,1)))

(Do adjust the formula according to your ranges and locale)

CodePudding user response：

Try rank(), like this:

=rank(K6; unique(K$6:K$44); true) <= 2