Conditional formatting for more than five in a row - Is there a better way?

[Jayem1]

Hello,

I have a column of a 1,000 randomly generated numbers where each cell shows one of two results, "1" or "-1".
I want to highlight only the cells that contain the same results of 5 or more in a row.

I also want to be able to highlight higher amounts like 8, 12 or 20 in a row.

Currently I have this formula for 5 in a row:


=OR(AND(A5=A1, A5=A2, A5=A3, A5=A4, A5=A5), AND(A5=A2, A5=A3, A5=A4, A5=A5, A5=A6), AND(A5=A3, A5=A4, A5=A5, A5=A6, A5=A7), AND(A5=A4, A5=A5, A5=A6, A5=A7, A5=A8), AND(A5=A5, A5=A6, A5=A7, A5=A8, A5=A9))


I want to know if there is a better way to do it or do I just use this basic format and keep building it out for the larger consecutive amounts I need?

Thank you.

 

[pgc01]

Hi

I'd use in A1 the formula:

=MAX(IF(ROW(A1)-ROW(INDIRECT("1:5"))>=0,COUNTIF(OFFSET(A1,1-ROW(INDIRECT("1:5")),0,5),A1)))=5

and copy the format down

 

[Jayem1]

Thank you that works perfectly. I have a few clarifications and a couple more variables to ask about.


1. If my data starts at 'B8' is it correct to change all occurrences in the formula of 'A1' to 'B8'?


2. Is it correct to assume that if I want to format consecutive repeats of 9-in-a-row (instead of 5) that I just change all the occurrences in the formula of '5' to an '8'?


3. Is there a way to change it to only format occurrences of 5-in-a-row but no more than that?


4. I have an issue with back to back occurrences of five "1"'s in-a-row followed by five "-1"'s in-a-row showing up as one large highlighted block so I want to format the groups of "1"'s in one color and the groups of "-1"'s in another. Can we tell the formula to only highlight five "1"s in a row? I would like to be able to do that for both current formula of five or more and for the new formula of just five in-a-row if possible.



Thank you again this is great help.

 

[Jayem1]

Correction: In question 2, I meant to say at the end, " '5' to a '9' " (not '8').