## Find numbers in close proximity to a given number

This article demonstrates how to apply Conditional Formatting formula to a cell range, it finds cells that are in close proximity to a given set of numbers based on a range value.

The image above shows 4 values in cell range B3:E3, the range number is in cell G3. A conditional formatting formula highlights cells in cell range B5:E19 that contain a number that is near at least one of the four values in cell range B3:E3.

For example, the image above shows 34, 57, 93 and 75. The range is 4. If 2 which is the cell value in B5 is in proximity with 34, 57 , 93 or 75 then cell B5 is highlighted green.

Number 34 has a higher bound 34 +4 = 38 and a lower bound of 34 - 4 = 30. 2 is not between 30 and 38.

Number 57 has a higher bound 57 + 4 = 61 and a lower bound of 57 - 4 = 53. 2 is not between 53 and 61.

Number 93 has a higher bound 93+ 4 = 97 and a lower bound of 93 - 4 = 89. 2 is not between 89 and 97.

Number 75 has a higher bound 75+ 4 = 79 and a lower bound of 75 - 4 = 71. 2 is not between 71 and 79. Cell B5 is highlighted red.

This article is inspired by a question found here.

Is this possible?Something like Abs(any number in array-any number in row < 4, Highlight green, highlight red) I think I have to utilize index or match functions but I'm having difficulty.

### Conditional formatting formulas

Green:

Red:

You can also use this formula for highlighting cells red.

**How to apply conditional formatting formulas**

- Select cell range (B5:E19)
- Go to tab "Home" on the ribbon.
- Click on the "Conditional formatting" button.
- Click "New Rule...".

- Select "Use a formula to determine which cells to format".
- Paste =OR(ABS(B5-$B$3:$E$3)<$G$3) to Format values where this is TRUE:

- Click "Format" button
- Go to tab "Fill"
- Select a color
- Click ok
- click ok

**Explaining "green" formula in cell A4**

*Step 1 - Subtract **value in cell B5 with **array of values specified in $B$3:$E$3*

B5-$B$3:$E$3

becomes

2-{34, 57, 93, 75}

and returns

{-32, -55, -91, -73}

*Step 2 - Return the absolute value of a number*

The ABS function removes the minus sign from a number.

ABS(B5-$B$3:$E$3)

becomes

ABS({-32, -55, -91, -73})

and returns

{32, 55, 91, 73}

*Step 3 - Check if values are less than value in cell G3*

The less than character < lets you check if a number is less than another number.

ABS(B5-$B$3:$E$3)<$G$3

becomes

{32, 55, 91, 73}<4

and returns

{FALSE, FALSE, FALSE, FALSE}

This means that the value in cell B5 is not in proximity within any of the numbers in cell range B3:E3.

*Step 4 - Return FALSE only if all values are FALSE*

The OR function returns TRUE if at least one of the boolean values in the array is TRUE and FALSE if all values are FALSE.

OR(ABS(A4-$A$2:$D$2)<$F$1)

becomes

OR({FALSE, FALSE, FALSE, FALSE})

and returns FALSE. Cell B5 is not highlighted green.

The conditional formatting formula applied to cell range F3:F13 highlights all cells containing text. Here is how I did it: […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Count Conditionally Formatted cells

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Highlight dates in a date range

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]

### One Response to “Find numbers in close proximity to a given number”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Thanks Oscar, Huge help!