Function for Trends/Streaks?

[moneyrunner]

If I wanted to count cells that occurred in a trend, what function could I use?

For example, if I am keeping track of wins and losses, and my cells go
W, L, W, W, W

How can I count to show a 3 win streak that would go to 0 if an L were to occur?

 

[Matty]

Hi,

Just to be clear, you want to know the length of the last winning streak but only if the running streak is still occurring, i.e. the last value in the range is a "W"?

Matty

 

[moneyrunner]

Correct. If an "L" were to occur next, it would go to zero.

Even better, if I could count losing streaks as well.

Ex. - If my cells were to look like L, W, W, W, L, L

Then it would show a losing streak of 2 or a value of -2.

 

[Matty]

Hi,

Assuming your data resides in A1:F1, perhaps:

=IF(LOOKUP(REPT("z",255),A1:F1)="L",Value("-"&LOOKUP(9.99999999999999E+307,FREQUENCY(IF(A1:F1="L",COLUMN(A1:F1)),IF(A1:F1="W",COLUMN(A1:F1))))),LOOKUP(9.99999999999999E+307,FREQUENCY(IF(A1:F1="W",COLUMN(A1:F1)),IF(A1:F1="L",COLUMN(A1:F1)))))

Though there's probably a more succinct way of doing this...

Matty

 

[moneyrunner]

Thanks for the help Matty! Does anyone else care to chime in on a simpler way?

 

[Stephen_IV]

=MAX(FREQUENCY(IF(A1:F1<>"L",ROW(A1:F1)),IF(A1:F1="L",ROW(A1:F1))))

Try this array formula using Ctrl+Shift+Enter

 

[Matty]

Hi moneyrunner,

I forgot to mention that my formula required committing with Ctrl+Shift+Enter. not just enter. Given the requirement you've asked for, it should do what you need it to.

Hi Stephen_IV,

Given a range of A1:F1, you would need to use COLUMN and not ROW for that formula to work as I think you meant it to, but unless I have misunderstood moneyrunner's requirement, I don't think this delivers what is required anyway (it will show the longest streak and not the nature of the last streak).

Matty

 

[Stephen_IV]

~

 

[Stephen_IV]

Matty you are so right. I did not read all the way thru! I found a formula from Harlan Grove that works for verticle not horizontal ranges. Maybe someone could modify it to work. This shows the last streak

Book3 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	W							4
2	L
3	W
4	W
5	L
6	W
7	L
8	L
9	L
10	L
11	W
12	W
Sheet1

=LOOKUP(2,1/(A1:A12="L"),ROW(A1:A12))-LOOKUP(2,1/(OFFSET(A1:A12,0,0,LOOKUP(2,1/(A1:A12="L"),ROW(A1:A12))-2,1)="W"),ROW(A1:A12))

Thanks Stephen!

 

[Matty]

Hi,

That formula (in its current form) delivers the length (count) of the last streak of Ls (4), but I think moneyrunner wanted a to know the length of the last streak, which would be 2. If the last 2 Ws were Ls, my reading of their request was that this should show as -2.

Since moneyrunner hasn't responded, perhaps they've already found a solution to this problem.

Matty