Calculate longest unbeaten/winless streak in excel

[HawkeyeBC]

I have a sheet that is tracking win, loss, and ties for a fantasy baseball team. I am trying to display the longest winning, losing, and unbeaten/winless streak.

For the win streak, I used the array {=MAX(FREQUENCY(IF(A2:A10="W",ROW(A2:A10)),IF(A2:A10<>"W",ROW(A2:A10))))] where column A is where the W/L/T is tracked.

How would I add in "T" to the formula so that the ties count toward the unbeaten streak? In the sample below, the longest winning streak should be 3, the longest losing streak should be 2, and the longest unbeaten streak should be 4. I am trying to avoid using a helper column but would do it if it makes things easier.

W
W
W
T
L
L
W
T
W
W

 

[kevin9999]

Book1
	A	B	C
1	W
2	W	3	Current formula
3	W
4	T	4	W or T (therefore not L)
5	L
6	L	2	Losing streak
7	W
8	T
9	W
10	W
11
Sheet2

Cell Formulas
Range	Formula
B2	B2	=MAX(FREQUENCY(IF($A$1:$A$10="W",ROW($A$1:$A$10)),IF($A$1:$A$10<>"W",ROW($A$1:$A$10))))
B4	B4	=MAX(FREQUENCY(IF($A$1:$A$10<>"L",ROW($A$1:$A$10)),IF($A$1:$A$10="L",ROW($A$1:$A$10))))
B6	B6	=MAX(FREQUENCY(IF($A$1:$A$10="L",ROW($A$1:$A$10)),IF($A$1:$A$10<>"L",ROW($A$1:$A$10))))

 

[HawkeyeBC]

Book1
	A	B	C
1	W
2	W	3	Current formula
3	W
4	T	4	W or T (therefore not L)
5	L
6	L	2	Losing streak
7	W
8	T
9	W
10	W
11
Sheet2

Cell Formulas
Range	Formula
B2	B2	=MAX(FREQUENCY(IF($A$1:$A$10="W",ROW($A$1:$A$10)),IF($A$1:$A$10<>"W",ROW($A$1:$A$10))))
B4	B4	=MAX(FREQUENCY(IF($A$1:$A$10<>"L",ROW($A$1:$A$10)),IF($A$1:$A$10="L",ROW($A$1:$A$10))))
B6	B6	=MAX(FREQUENCY(IF($A$1:$A$10="L",ROW($A$1:$A$10)),IF($A$1:$A$10<>"L",ROW($A$1:$A$10))))

Amazing! It totally worked! Thank you so much

 

[kevin9999]

Amazing! It totally worked! Thank you so much

You're welcome & thanks for the feedback

 

[HawkeyeBC]

For the middle part (W or T)...what if I wanted to ignore blank cells? I want to omit those from being counted until there is something entered into the cell.

 

[HawkeyeBC]

For the middle part (W or T)...what if I wanted to ignore blank cells? I want to omit those from being counted until there is something entered into the cell.

It's counting the blank cells as "Not L" for the unbeaten streak formula. How would I omit those until there is data entered?

 

[kevin9999]

skm95.xlsx
	A	B	C
1	W
2	W	3	Current formula* ADJUSTED
3
4	W
5	T	4	W or T (therefore not L)
6	L
7	L	2	Losing streak
8	W
9	T
10	W
11	W
Sheet2

Cell Formulas
Range	Formula
B2	B2	=MAX(FREQUENCY(IF(($A$1:$A$11="W")*($A$1:$A$11<>""),ROW($A$1:$A$11)),IF(($A$1:$A$11<>"W")*($A$1:$A$11<>""),ROW($A$1:$A$11))))
B5	B5	=MAX(FREQUENCY(IF(($A$1:$A$11<>"L")*($A$1:$A$11<>""),ROW($A$1:$A$11)),IF(($A$1:$A$11="L")*($A$1:$A$11<>""),ROW($A$1:$A$11))))
B7	B7	=MAX(FREQUENCY(IF(($A$1:$A$11="L")*($A$1:$A$11<>""),ROW($A$1:$A$11)),IF(($A$1:$A$11<>"L")*($A$1:$A$11<>""),ROW($A$1:$A$11))))

 

[kevin9999]

Did the latest suggestion work for you?

 

[HawkeyeBC]

It did yes! Thanks again so much. Very much appreciated.