Conditional Formatting - Highlight the closest value to a number

speeder2k3

New Member
Joined
Sep 22, 2014
Messages
7
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!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Canapone

Active Member
Joined
May 10, 2007
Messages
463
[HR][/HR]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

New Member
Joined
Sep 22, 2014
Messages
7
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.


[HR][/HR]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

Active Member
Joined
May 10, 2007
Messages
463
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,696
Messages
5,470,221
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top