Need a formula to get the frequency of consecutive Mode values

[nmansoorn]

Hi guys,

This my first post here and I am in desperate need of help.

I need a formula to look up a range and count (frequency) the most frequent value (mode) that are consecutive. Below is an example of the values:

5,0,5,0,10,0,5,10,0,0,0,5,12,5. The formula should give me 1 because the mode is 5 and none of them are occurring consecutively.

I have come this far so far:
=MAX(FREQUENCY(IF(B1:AZ1=IFNA(MODE(B1:AZ1),0),COLUMN(B1:AZ1)),IF(B1:AZ1=0,COLUMN(B1:AZ1))))

This array formula gives me 2 because of the last 5,12,5 that is occurring consecutively but the middle value is not a mode value, so it should not count this as consecutive. It should count as consecutive only when the mode value is side by side. If there are 2 of them side by side, it should return 2, and if there are 3 side by side, it should return 3, and so on.

The highlighted column is where the formula is. Also, I am certain there is a better way of doing this but this is all I could find right now.

Sample of the data attached (I hope I did it correctly).

Thank you guys so much!!

[FlameRetired]

Hello nmansoorn. Welcome to the forum.

Try array entering this formula.

Please Login or Register  to view this content.

[nmansoorn]

Sorry for the delayed response. This works like a charm! Thank you so much!!!!!!!!

[FlameRetired]

You are welcome.
Thank you for the feedback and added rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.