Conditional Formatting highlight consecutive cells above a specific value

  • Hello all,

    I'm trying to apply conditional formatting across this entire sheet to highlight three consecutive cells above 4.5.

    I thought I had it with = and(e3>4.5,f3>4.5,g3>4.5)

    it doesn't work. I've tried a couple of different formulas and it always highlights pieces rather than the three or more in a row that are above the 4.5 constant



    help please! I'm sure someone here can get it.. I sure couldn't..

  • You need to test 3 times, and exclude groups that have text (like NA), so:

    AND ( currcell > 4.5 , celltoright > 4.5 , cell2toright > 4.5 )

    AND ( celltoleft > 4.5 , currcell > 4.5 , celltoright > 4.5 )

    AND ( cell2toleft > 4.5 , celltoleft > 4.5 , currcell > 4.5 )

    combined with a SUMPRODUCT with NOT and ISTEXT to make sure all 3 cells are not text.


    See attached.


    Trial Sheet for Brooke.xlsx