Function for Trends/Streaks?

moneyrunner

New Member
Joined
Nov 1, 2010
Messages
5
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?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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

New Member
Joined
Nov 1, 2010
Messages
5
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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Hi,

Assuming your data resides in A1:F1, perhaps:

Code:
=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
 
Last edited:

moneyrunner

New Member
Joined
Nov 1, 2010
Messages
5

ADVERTISEMENT

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

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
968
=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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710

ADVERTISEMENT

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

Well-known Member
Joined
Mar 17, 2003
Messages
968
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
ABCDEFGH
1W4
2L
3W
4W
5L
6W
7L
8L
9L
10L
11W
12W
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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,527
Messages
5,523,391
Members
409,515
Latest member
chemitek

This Week's Hot Topics

Top