## 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.
- Press with mouse on the "Conditional formatting" button.
- Press with left mouse button on "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:

- Press with left mouse button on "Format" button
- Go to tab "Fill"
- Select a color
- Press with left mouse button on ok
- press with left mouse button on 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.

### Cf basic formulas category

In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]

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

Table of Contents Highlight empty cells Explaining CF formula How to apply conditional formatting Formulas returning nothing Cells with hidden […]

### Conditional formatting category

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

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

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

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 […]

The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]

This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]

The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]

In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

The image above demonstrates a conditional formatting formula that highlights duplicateÂ items based on date. The first instance is not highlighted, […]

A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]

In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]

The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]

This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]

In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]

The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]

## Functions in this article

More than 1300 Excel formulas

## Conditional Formatting categories

## Excel categories

### 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!