Merit asks:

I have a large array of numbers and a row of numbers. I want each number in the array to become higlighted one color if it is within 4 of any number in the row and another if it is not. Is this possible? Something like Abs(anynumber in array-any number in row < 4, Highlight green, highlight red) I think I have to utilize index or match functions but im having difficulty. Thanks!

Answer:

Conditional formatting formulas

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

Red:=AND(ABS(A4-$A$2:$D$2)>=$F$1)

How to apply conditional formatting formulas

  1. Select cell range (A4:D18)
  2. Go to tab "
  3. Click "Conditional formatting" button
  4. Click "New Rule..."
  5. Click "Use a formula to determine which cells to format"
  6. Paste "green" formula in field
  7. Click "Format" button
  8. Go to tab "Fill"
  9. Select a color
  10. Click ok
  11. click ok
Repeat step 4 to 11 with "red" formula.

Explaining "green" formula in cell A4

Step 1 - Subtract array of values ($A$2:$D$2) from value in cell A4

A4-$A$2:$D$2

becomes

2-{34, 57, 93, 75}

and returns

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

Step 2 - Return the absolute value of a number

ABS(A4-$A$2:$D$2)

becomes

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

and returns

{32, 55, 91, 73}

Step 3 - Check if values are less than value in cell $F$1

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

becomes

{32, 55, 91, 73}<4

and returns

{FALSE, FALSE, FALSE, FALSE}

Step 4 - Return FALSE only if all values are FALSE

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

becomes

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

and returns FALSE. Cell A4 is not highlighted green.

Download *.xlsx file
Merit.xlsx

Related posts:

Highlight specific time ranges in a weekly schedule in excel

Highlight overlapping date ranges using conditional formatting in excel

Excel: Count and sum values between specific ranges in a column

Highlight unique distinct values in two ranges combined using conditional formatting in excel

Sort numeric values by closest to farthest to a specific number in excel (array formula)