0

This is a complex ask, so I apologize if it's unclear and will be happy to clarify. I have a series of numbers (dice rolls, in this case), and I need a formula to do the following:

  1. Find groups of consecutive SAME numbers (i.e. two or more 1's in a row)
  2. identify the largest of those groups
  3. count how many occurrences of that number are in the largest group

And just to clarify, I don't need all three of those as output - I just need #3 as a number.

I've made a copy of the sheet here: https://docs.google.com/spreadsheets/d/1CS9gxUvRAgFFyKjsyrQHgXDZKaUyYvzC16sCZED1a7Y

Any help with this would be greatly appreciated!

1 Answer 1

1

Have a try with this:

=arrayformula(let(
dicesides,20,
results,tocol(A2:A,1),
rollnum,sequence(counta(results)),
streaks_,lambda(x,frequency(if(results=x,rollnum),if(results=x,0,rollnum))),
maxstreakperdiceside,map(sequence(dicesides),lambda(roll,max(streaks_(roll)))),
max(maxstreakperdiceside)))

For each side of the dice, we are calculating the lengths of all the streaks of that side that are observed in the results, then calculating the maximum streak length per side, then finally calculating the maximum of the maximums.

1
  • You, sir, are a genius. Thank you so much, very much appreciated!
    – pub13
    Nov 2 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.