Conditional formatting cells close to minimum of the column

Question

Currently I have a custom formula in Google Spreadsheet as following:

=ArrayFormula(E1=MIN(if(ISERROR(FIND("NO",E$1)),E$2:E$100)))

Which highlights the cells with the minimum value in columns (without "NO" as part of the column name, E$1). And I would like to add another conditional formatting that highlights cells with values close to the column's minimum (say, given a range of [0, +0.1]). How should I edit the existing formula?

For example, a column with values ["AVG1", 1.1, 2.3, 0.8, 0.85] would have 0.8 highlighted, and I would like 0.85 also highlighted (since 0.85 - 0.8 < 0.1).

p.s. a column with values ["NO_1", 1, 2, 3, 4] would have nothing highlighted since "NO_1" contains "NO" as part of the column name.

Answer 1

I dropped your formula into a dummy sheet and noticed one slightly unexpected behavior where it will flag a value of 0 (or a blank cell) as being the minimum value even in a column labeled with "No". If this is undesired behavior I'd recommend a slight tweak in returning a string to force an error if you don't want the data looked at in your if() function instead of an empty range (since the minimum value of an empty range seems to be considered 0).

(The use of ArrayFormula() is also not necessary here since MIN() is a function which can accept a range by default, but I've left it in since I'm not sure if you had another reason for wanting it.)

=ArrayFormula(E1=MIN(if(ISERROR(FIND("NO",E$1)),E$2:E$100,"Skip")))

So now onto your actual question. You've already mapped out all the logic for it in your post, you simply need to compare the difference between the minimum value and the current cell to .1, instead of directly comparing the two values.

=ABS(MIN(if(ISERROR(FIND("NO",E$1)),E$2:E$100,"Skip"))-E1)<.1

This will return a value of TRUE whenever the difference between the current cell and the minimum is less than .1, and FALSE otherwise.