Excel Formula for max repeated number?

Question

I created a spreadsheet where I have 1000 cells randomize a number output of -1 or x. Where X is determined by an input and is greater than 0. All the cells will either display -1 or x, and are all random, so I could have -1, x, -1,-1, x,x,x, etc.

My question is how to determine the longest string of -1's repeated, or longest string of x's repeated. I can scroll through the list of course, but that is inefficient and I could miss something. Is there a formula that can tell me the maximum series of -1's next to each other?

expletive_xom · Answer

here is an array formula for the -1

=MAX(--(FREQUENCY(IF($A$1:$A$1000=-1, ROW($A$1:$A$1000)), IF($A$1:$A$1000<>-1, ROW($A$1:$A$1000)))))

its an array formula so hit Ctrl Shift Enter...if you hit all 3 properly you will get curly brackets { and } around the formula.
you can replace both -1 in the formula with x or use a cell reference if you want.

Excel Formula for max repeated number?

1 Answer