highlight three closest values, higher or lower

[jcmccaughan]

hello all,

I have tried to tweak some of these formulas that are similar to what i am searching for but with no luck.

I have values in A1:A19 with a reference in
B1. I would like conditionally format the three rows A:D that hold the closest values, with higher or lower not being relevant.

can you help me out?

thanks,
J.

 

[AlphaFrog]

Try this. Make three conditions and change the red 1 to a 2 and 3 for the other two formulas.

=SMALL(INDEX(ABS($A$1:$A$19-$B$1),),1)=ABS($A1-$B$1)

 

[Steven Schoors]

You might calculate the ABS difference and then highlight with conditional formating the lowest 3. (Format only top or bottom ranked values)