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?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
=MAX(FREQUENCY(IF(A1:F1<>"L",ROW(A1:F1)),IF(A1:F1="L",ROW(A1:F1))))

Try this array formula using Ctrl+Shift+Enter
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top