Search code examples
google-sheetsgs-conditional-formatting

Google Sheets - Conditional Formatting on Closest Value

I have a column of data and a target value and am wanting to apply a rule that will highlight the closest value to that target value from the column of data possible. I have tried a few different forumla and nothing has worked so far. This is what I am currently working with:

Target Number is in I3
Data is in I4:I24

=ABS($I$3-I4)=MIN(ABS($I$3-$I4:$I24))

This is all being done through Google Sheets (not sure if this is relivant but thought it couldn't hurt being included)

Exact image of the conditional formatting from the sheet

Solution

  • First of all, let's change $I4:$I24 to $I$4:$I$24, so that this array is the same for all cells that the formula is applied to.

    The - operator only works for numbers, not arrays. So when you subtract an array from a number, it returns just the value minus the first number in the array. The same goes for the ABS function - it can't take an array.

    To instead have the - operator and ABS function apply to each element of the array individually, you need to use the ARRAYFORMULA function. To do this, wrap the ABS($I$3-$I$4:$I$24) in ARRAYFORMULA - that is, ARRAYFORMULA(ABS($I$3-$I$4:$I$24)). Now, ABS and - apply to each element of the array individually, and return an array of all the results. This can then be passed into MIN.

    Now we get

    =ABS($I$3-I4)=MIN(ARRAYFORMULA(ABS($I$3-$I$4:$I$24)))