Conditional Formatting - Highlight the closest value to a number

[speeder2k3]

Hello everyone!

I need your help for the following example situation.
I have a column of values, lets say from A1 to A5 (numbers with 3 digits after coma) and the cell B1 with the value 2.255:
A1 - 0.856
A2 - 1.245
A3 - 1.455
A4 - 2.250
A5 - 2.260

I want to highlight the cell from A1 to A5 which is closest to B1. If there are two cells equally distant to highlight both.
According to B1=2.255 I should highlight both A4 and A5.
If the B1 value changes to lets say 1.188 then A2 should highlight, being the closest.
I need to do this only with conditional formatting formula. I cant alter the structure of the file and insert another columns to help in the process.
Thank you very much!

 

[Canapone]

---

Hi,

you could apply as new rule for conditional formatting in A1:A5


=ABS(A1-$B$1)=MIN(ABS($A$1:$A$5-$B$1))


Cheers

 

[speeder2k3]

Thanks Canapone, that worked.
How about if I need to highlight one closest value on 5 separated columns?
Example: A1:A5, C1:C5, E1:E5, G1:G5, I1:I5 are the columns. The numbers in the columns are sorted ascending starting with A1 the smallest to I5 the highest (this probably it is not important).
We are assuming the previous example and looking for the closest value of B1.

I tried the formula which you gave me in this situation but I got an error saying that there are too many arguments for this formula. While selecting each column with the mouse+CTRL the formula turned to:
=ABS(A1-$B$1)=MIN(ABS($A$1:$A$5,$C$1:$C$5,$E$1:$E$5,$G$1:$G$5,$I$1:$I$5-$B$1))

I tried to select all the cells between the A1 to I5 and use the formula but in the columns B, D, F, G and H there is text so it doesn't highlight anything (if I delete the text it works but I cant alter the structure of the table).

Thank you.

---

Hi,

you could apply as new rule for conditional formatting in A1:A5


=ABS(A1-$B$1)=MIN(ABS($A$1:$A$5-$B$1))


Cheers

 

[Canapone]

Hi,

I'm trying this formula as new rule (delete the old one)

=ABS(A1-$B$1)=MIN(ABS($A$1:$A$5-$B$1),ABS($C$1:$C$5-$B$1),ABS($E$1:$E$5-$B$1),ABS($G$1:$G$5-$B$1))

applied to

=$A$1:$A$5,$C$1:$C$5,$E$1:$E$5,$G$1:$G$5


Hope it helps

 

[speeder2k3]

Canapone this is very helpful. Thank you very much for your help.

 

[Canapone]

Hi,

thanks for providing kind feedback.

Cheers