# Function for Trends/Streaks?

#### moneyrunner

##### New Member
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?

### 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
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
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
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

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

#### Stephen_IV

##### Well-known Member
=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

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

~

Last edited:

#### Stephen_IV

##### Well-known Member
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
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

Replies
3
Views
31
Replies
23
Views
2K
Replies
27
Views
144
Replies
12
Views
301
Replies
11
Views
102

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