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?

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

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


    Quote from Wigi

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


    It is a trading method that I use that has to exclude the immediate cells :)


    If we include the cells, is there an easy way to do it?

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!