Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Excel Formula for max repeated number?
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?
1 Answer
- expletive_xomLv 71 week ago
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.
