Conditional Formatting - Highlight closest value in excel

G

GA3

New Member
Joined
Nov 19, 2009
Messages
41
Office Version
  1. 365
  2. 2010
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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hi,

Maybe there is a better solution, but i think this works

Create two helper columns like these

AW AX
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>N10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>0,15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>R10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0,1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>V10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0,5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Z10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1,1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>AD10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1,3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>AH10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0,8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>AL10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>AP10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1,2</TD></TR></TBODY></TABLE>

In AW1:AW8 enter as text the cell addresses

Formula in AX1
=ABS(INDIRECT(AW1)-$J$10)
copy down til AX8

Then select the range N10:AP10
CF>New Rule>Use a formula to...
and enter this formula
=ADDRESS(ROW(),COLUMN(),4)=INDEX($AW$1:$AW$8,MATCH(MIN($AX$1:$AX$8),$AX$1:$AX$8,0))

Pick, e.g, Fill ---> Yellow
Ok, Ok

HTH

M.
 
Upvote 0
GA3 said:
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!
Click to expand...
First, tell us what version of Excel you're using.

Ok, let's assume J10 = 2

N10 = 1
R10 = 3

Which cell should be highlighted?
 
Upvote 0
Hi,

Improving the formula of my previous post to get more than one number that are equally closer to J10.

Using the helper columns AX and AW

Select N10:AP10
CF>New Rule>Use a formula to..
Formula
=AND(MATCH(ADDRESS(ROW(N10),COLUMN(N10),4),$AW$1:$AW$8,0),ABS(N10-$J$10)=MIN($AX$1:$AX$8))

M.
 
Upvote 0
TV...I am using Excel 2007.

Marcello, I have 20,000 rows that I need to apply this to. How would that affect the helper table?
 
Upvote 0
The answer would be both if they are equidistant.

I have come up with a solution that will work for me 90% of the way. The other 10% could be considered negligible.

In each of my cells, I have used 2 conditional formatting rules. I have set each cell to highlight if the value in the cell (N10, R10, V10, Z10, AD10, AH10, AL10, AP10) is within 5 of the value in J10.

(These are actually retail price points so I have set the conditions to be within .05 of the compare to value in J10. The Price points are usually within a .10 increment so it will catch 90% of them).
 
Upvote 0
Hi GA3,

I didnt know you have so many rows. You already have a solution, i know, but i think i also created one.

To reduce the CF final formula, in column AS (for example) i inserted this formula to get the minimum of each row
=MIN(ABS(N10-J10),ABS(R10-J10),ABS(V10-J10),ABS(Z10-J10),ABS(AD10-J10),ABS(AH10-J10),ABS(AL10-J10),ABS(AP10-J10))
copy down

In AY1:BF1 (for example) i put the relevant column-numbers (columns N, R, V, Z, AD, AH, AL, AP)

AY AX.....BF
<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>26</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>34</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>38</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>42</TD></TR></TBODY></TABLE>


At last, selecting, N10:AP?
CF formula
=AND(MATCH(COLUMN(N10),$AY$1:$BF$1,0),ABS(N10-$J10)=$AS10)

HTH

M.
 
Upvote 0
GA3,

Forgot to say
The formulas in column AS begin at AS10

If you dont want a helper column, you can substitute in CF formula $AS10 by the formula
=MIN(ABS($N10-$J10),ABS($R10-$J10),ABS($V10-$J10),ABS($Z10-$J10),ABS($AD10-$J10),ABS($AH10-$J10),ABS($AL10-$J10),ABS($AP10-$J10))

M.
 
Upvote 0
Marcelo Branco said:
GA3,

Forgot to say
The formulas in column AS begin at AS10

If you dont want a helper column, you can substitute in CF formula $AS10 by the formula
=MIN(ABS($N10-$J10),ABS($R10-$J10),ABS($V10-$J10),ABS($Z10-$J10),ABS($AD10-$J10),ABS($AH10-$J10),ABS($AL10-$J10),ABS($AP10-$J10))

M.
Click to expand...


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
385
chris_bosten
C
S
  • Question Question
Help with conditional formatting using a pivot table with dynamic ranges
Replies
1
Views
115
RoryA
RoryA
M
  • Question Question
3 columns - Highlight lowest value - But only if true lowest not a tie.
Replies
5
Views
98
dreid1011
dreid1011
Excelwhatsthat
  • Question Question
Scroll enabled to minimise table view...but need conditional formatting
Replies
0
Views
98
Excelwhatsthat
Excelwhatsthat
Y
  • Solved
VBA to highlight duplicates in columns
Replies
2
Views
241
Yamasaki450
Y

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top