Excel 2010 or Excel 2003 : Conditional format for consecutive cell criteria

Hello, brand new here. Hoping to get some advice on conditional formatting. I am an amateur with respect to Excel formulas.

I need to create a highlighting mechanism which compares data (entered daily) to a fixed value. I only need the new data to become highlighted when it is greater then then the fixed value and then only when this has occurred on five consecutive dates. (4 in row or below, no highlighting required)

I can create the highlighting effect on single entries but the "5 in a row" is causing me problems. BIG!

any help or advice would be greatly appreciated.

carol_sarah_759

nope, nothing happens

Hello
If your range of values begins in say cell A1, from A5 down select the range and insert the CF formula:

Please Login or Register  to view this content.

'Target' is the cell with the fixed value you wish to use as criteria, or you could hard code it in to the formula.

Does this help?
DBY

The cell range is E4:E262. The reference cell value is 0.99. When I used the formula =COUNTIF(A1:A5,"<"&Target)>4 the first cell E4 (value 0.98) was highlighted. No other values in the set (which were below 0.99) were highlighted.

I need values which are above or below the Target cell (0.99) to become highlighted only when they occur in 5 consecutive sets.

Ex. 1.00
1.00
1.00
1.00
1.00
Highlighted

Ex. 1.00
1.00
1.00
1.00
0.98
Set is not highlighted

Sorry at the moment I can't think of a solution for this. You can highlight sequences where they exceed 4 by adapting the formula I provided but not the whole sequence.

DBY

no problem, thanks for looking at this for me.