How to count longest sequence

Question

I have a column in Excel that contains either Win or Lose and I'd like to be able to display the longest sequences of Win or Lose within that column.

For example in this sequence

LLWLLWWLLLWWWWLLW

the results would show Longest Winning Run = 4 & Longest Losing Run = 3

Many thanks in advance

Tony

Answer 1

Assuming your W & L are in row 1 starting in column A (Adjust the ranges for your data)

These are array formulas so you need to enter them using CTRL-SHIFT-ENTER rather than just ENTER. If successful a pair of {} will be put around the formula

For wins

=MAX(FREQUENCY(IF(A1:P1="W",COLUMN(A1:P1)),IF(A1:P1<>"W",COLUMN(A1:P1))))

For Losses

=MAX(FREQUENCY(IF(A1:P1="L",COLUMN(A1:P1)),IF(A1:P1<>"L",COLUMN(A1:P1))))

Answer 2

Further to previous answer, you say your data is in a column so use this instead

=MAX(FREQUENCY(IF(A1:A20="W",ROW(A1:A20)),IF(A1:A20<>"W",ROW(A1:A20))))

Answer 3

Many thanks for 2 methods, I've tried method 2 using a new sheet and columns A1:A20 but see an error .

It tells me "A value used in the formula is the wrong data type"

Show calculation steps highlights =MAX(FREQUENCY(IF(A1:A20="W",ROW(A1:A20)),IF(A1:A20<>"W",ROW(A1:A20)))) 

The range A1:A20 is underlined and states "The next evaluation will result in an error"

=MAX(FREQUENCY(IF(#VALUE!****="W",ROW(A1:A20)),IF(A1:A20<>"W",ROW(A1:A20))))

I have double checked I've used the right cells etc

Thanks

Tony

Answer 4

Formula works perfectly, my mistake but in my defence I wasn't aware of the  { } requirements.

Thank you so much

Tony