Conditional Formatting - Highlight closest value in excel

[GA3]

I have a number in cell J10 and need to highlight the closest number to it from the following cells:N10, R10, V10, Z10, AD10, AH10, AL10, AP10.

I have found help from sites that show things in columns but I need to know how to do it in rows instead.

I have looked for a couple of hours and am very frustrated. Thanks for your help!

 

[Marcelo Branco]

Hello Experts,

I have a similar case, please help. Kindly refer to the following table.

[TABLE="width: 500, align: left"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CVAL
[/TD]
[TD]H4
[/TD]
[TD]H3
[/TD]
[TD]H2
[/TD]
[TD]H1
[/TD]
[TD]LP
[/TD]
[TD]L1
[/TD]
[TD]L2
[/TD]
[TD]L3
[/TD]
[TD]L4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]998.05
[/TD]
[TD]1014.25
[/TD]
[TD]1008.67
[/TD]
[TD]1004.32
[/TD]
[TD]1000.04
[/TD]
[TD]1000
[/TD]
[TD]997.35
[/TD]
[TD]993.25
[/TD]
[TD]990.27
[/TD]
[TD]990.28
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]








The nearest value to C2 among the following 9 values (same row) should be highlighted. How to achieve the same using Conditional Formatting?

Note: I have many such rows following this sample one.

Is 1000 (underlined) the expected result?
If so, could you clarify the logic behind? (997.35 is nearer than 1000)

M.

 

[amohamed81]

Thanks for the reply Marcello.

Yes, you are right, 1000 is the expected result.

The logic is LEAST POSITIVE DIFFERENCE, as explained below:

[TABLE="width: 640"]
<colgroup><col span="10"></colgroup><tbody>[TR]
[TD]CVAL[/TD]
[TD]H4[/TD]
[TD]H3[/TD]
[TD]H2[/TD]
[TD]H1[/TD]
[TD]PP[/TD]
[TD]L1[/TD]
[TD]L2[/TD]
[TD]L3[/TD]
[TD]L4[/TD]
[/TR]
[TR]
[TD]998.05[/TD]
[TD]1014.25[/TD]
[TD]1008.67[/TD]
[TD]1004.32[/TD]
[TD]1000.04[/TD]
[TD]1000[/TD]
[TD]997.35[/TD]
[TD]993.25[/TD]
[TD]991.54[/TD]
[TD]990.28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16.2[/TD]
[TD]10.62[/TD]
[TD]6.27[/TD]
[TD]1.99[/TD]
[TD]1.95[/TD]
[TD]-0.7[/TD]
[TD]-4.8[/TD]
[TD]-6.51[/TD]
[TD]-7.77[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]H4-CVAL[/TD]
[TD]H3-CVAL[/TD]
[TD]H2-CVAL[/TD]
[TD]H1-CVAL[/TD]
[TD]PP-CVAL[/TD]
[TD]L1-CVAL[/TD]
[TD]L2-CVAL[/TD]
[TD]L3-CVAL[/TD]
[TD]L4-CVAL[/TD]
[/TR]
</tbody>[/TABLE]

Hope the explanation is clear!
[TABLE="width: 576"]
<tbody>[TR]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe something like this


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

CVAL​

[/TD]
[TD]

H4​

[/TD]
[TD]

H3​

[/TD]
[TD]

H2​

[/TD]
[TD]

H1​

[/TD]
[TD]

PP​

[/TD]
[TD]

L1​

[/TD]
[TD]

L2​

[/TD]
[TD]

L3​

[/TD]
[TD]

L4​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

998,05​

[/TD]
[TD]

1014,25​

[/TD]
[TD]

1008,67​

[/TD]
[TD]

1004,32​

[/TD]
[TD]

1000,04​

[/TD]
[TD]

1000​

[/TD]
[TD]

997,35​

[/TD]
[TD]

993,25​

[/TD]
[TD]

991,54​

[/TD]
[TD]

990,28​

[/TD]
[/TR]
</TBODY>[/TABLE]


Select D2:L2 (not C2:L2) being D2 the active cell, i.e., the one that is not shaded after the selection

Home > Conditional Formatting > New rule > Use a formula to determine which cells to format

insert this formula
=D2-$C2=MIN(IF($D2:$L2>=$C2,$D2:$L2-$C2))

pick the format you want (Font red for example)

Hope this helps

M.

 

[amohamed81]

Yes, its working!

Many thanks for the timely reply Marcello...

 

[Marcelo Branco]

Yes, its working!

Many thanks for the timely reply Marcello...

You are welcome and thanks for the feedback.

M.