How to count consecutive same values in a range of cells

[che]

I have 100 cells (4 columns by 25 rows) the contents of which are randomly generated to contain either a 1, 2 or 3. The cells are recalculated regularly. I am trying to determine/record the results of the number of times that 3 consecutive 1s, 2s or 3s appear, the number of times that 4 consecutive 1s, 2s or 3s appear....all the way to 20 consecutive appearances of 1s, 2s or 3s--to the extent such a run would be generated. I have tried like to come up with something and nothing seems to work. Also, because the numbers are recalculated, the results need to be tabulated/recorded in another cell before the cells are regenerated. If you can help, I would really appreciate it. Thanks a lot.

CC

 

[martinee]

Consecutive across the row or down the columns? Does it count if the last two values in 1 row match the first 2 in the next? Etc...some clarifications could help.

 

[sky_lennard]

... just curious, what's the formula you're using to generate the random numbers? (excel's "random" numbers aren't very random...)

Are you wanting to have the cells highlighted or just return a number for how many times you have a run of the same number?

Does a 3 in row 25 of column A and a 3 in row 1 of column B count as a match?

Thanks!!

-nate

 

[che]

Consecutive across the row or down the columns? Does it count if the last two values in 1 row match the first 2 in the next? Etc...some clarifications could help.

Sorry, consecutive down the columns and if the final cell in column 1 matches the first two cells in column 2 then it would be deemed to count. the numbers appear in cells d4:d28; g4:g28; i4:i28; l4:l28.

thanks

 

[che]

... just curious, what's the formula you're using to generate the random numbers? (excel's "random" numbers aren't very random...)

Are you wanting to have the cells highlighted or just return a number for how many times you have a run of the same number?

Does a 3 in row 25 of column A and a 3 in row 1 of column B count as a match?

Thanks!!

-nate

Nate-

I recognize the shortcomings of excel's random number tools. I used randbetween.

I don't need the cells highlighted, I just want to tabulate the number of times I have a run of the same number, with a run defined as at least 3 in a row.

The answer to you last question is yes.

Thanks for your help!

 

[che]

... just curious, what's the formula you're using to generate the random numbers? (excel's "random" numbers aren't very random...)

Are you wanting to have the cells highlighted or just return a number for how many times you have a run of the same number?

Does a 3 in row 25 of column A and a 3 in row 1 of column B count as a match?

Thanks!!

-nate

Nate-

I recognize the shortcomings of excel's random number tools. I used randbetween.

I don't need the cells highlighted, I just want to tabulate the number of times I have a run of the same number, with a run defined as at least 3 in a row. Ideally, I would have a table off to the side that would have at the top as a heading 3, 4, 5, 6 ....20 and beneath each heading it would count the number of instances that such experience occurred.

The answer to you last question is yes.

Thanks for your help!