Highlight Cell If Surrounding Cells Close To Value

Re: Highlight Cell If Surrounding Cells Are Close To That Cell Value

Well,
you could build up an =IF(OR(.... statement based on the one i've started in the attached.
But it's probably not the best answer out there and will likely have you pulling your hair out trying to get all the possible combinations. Unless you are a wizard at Nested IF statements.

I'm sure someone can come up with a better answer. But you never know.

KJ

Re: Highlight Cell If Surrounding Cells Are Close To That Cell Value

Why A9, A8,A7, A13, A14,A15 and not A10 & A12?

Re: Highlight Cell If Surrounding Cells Are Close To That Cell Value

Could you clarify what, exactly, you want compared before A11 is highlighted? Your first post mentions +2, +1, -2, -1, but your attachment only mentions the range -2 through +2. Is A11 to be highlighted if ANY of the cells you mentioned are in that range, or only if ALL of them are? Now you are mentioning A9 annd A11 - this is very confusing. If you can make it clear, the formula should be easy.

Re: Highlight Cell If Surrounding Cells Are Close To That Cell Value

You can do it with [cf]*[/cf]. Since you are looking at up to 4 cells above, select cell A5 and all the cells below it in column A, and using FORMAT > CONDITIONAL FORMATTING > FORMULA IS enter:

=(SUMPRODUCT((ABS((A1:A3)-A5)<=2)*1)+SUMPRODUCT((ABS((A7:A9)-A5)<=2)*1))>0

See [msc]*[/mcs]

and set your formatting options. (This highlights cells with a value in the tested cells that is within 2 of the target cell.)

See the attached.