Count consecutive most recent values.

Hi all.

I have tried and tried to find the answer to this, but every formula I have found doesn't seem to do exactly what I would like it to do!

I am looking for a formula that gives me the consecutive count of the most recent value in the string, as below;

L
W
W
W
L
W
L
L
W
W
W
W

W
W
W


My problem has been the gap where there is no value, the current work around I am trying to use starts the count again after the null value.

Any help would be great appreciated!

Cheers Crooza, this seems to be what I am chasing. But, the most recent value is the last, and it will add more below the last one week by week.

Just looking over the formula to see if I can reverse it perhaps?

Originally Posted by Crooza

Marvin, I think the OP wants that string of 7 Ws to show as 7 even though there is a blank row

Correct. I am looking for the result of 7 to show currently.

Hi VAMH2,

Looks like a Crows string of results to me!

I think we need to resort to VBA! Are you happy with that?

Please Login or Register  to view this content.

This macro will give you the result you want. It writes the result against the last entry (in Col B).

It could also be done as a UDF (user defined function) if you prefer.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Yes, changing the A to whatever the column is will work. It is looking for the last row where you have an entry.

However, you will need to edit one more thing. In the Cells() formulas, the second parameter is referring to the column.

I used 1 because it was easier to type than "A". Therefore, you can change these to the column number if you like, or the letter.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Originally Posted by David A Coop

I hope this helps, please let me know![/COLOR][/SIZE]

Still not 100%. I am using Column "AK", results will show up in Rows 4-30. When IF returns no results, I'm currently using "".

Thanks.

Have adjusted for use of column AK.

Please Login or Register  to view this content.

You will now the answer next to the last entry in Col AL

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Originally Posted by David A Coop

I hope this helps, please let me know!

Doesn't look like this is going to work.

I have just run some testing, and it seems because it is referencing a result from an IF statement, it doesn't pick up on it. Running it from a blank worksheet and just putting in the data manually it picks it up straight away.

Thanks for the help, but think it may only work with a formula, got too much going on for it to rely on VB it seems. Will see if Crooza was able to reverse what he did, as it seems like that just may work for me.

Cheers.

Originally Posted by Tony Valko

Try this...

Thanks for that. Currently it is returning a value of '81' (81 blank cells after last result). If I change the data to rows 4-30 where values appear, it returns '11' (empty cells after last result).

It seems that because the cells with no results are gathered by an IF statement, it still counts them as there is something in there.

Originally Posted by Tony Valko

There's always some detail that gets left out!

Originally I had forgotten to add that detail, but was mentioned a couple posts in. Anyway...

Success!

Awesome, thanks for the help. Been bugging me for ages, and just as 1 thing seemed to have worked the next minute it wasn't.

Thanks all in the thread for helping/trying.

Originally Posted by VAMH2

Doesn't look like this is going to work.

Sorry to hear that. I guess I would have to see the workbook to test it myself.

I see Tony has come up with an amazing formula! I knew that it would be an ARRAY formula, but too cleaver for me! (That's why he's a guru!) I'm going to unpack it to see how it works!

All the best.

David (a hopeful Demon supporter)

I received some inspiration from Tony when he suggested using OFFSET to get the dynamic range name.

Here is another formula:

=COUNTA(INDIRECT("A"&MAX(ROW(1:200)*(A1:A200=IF(INDIRECT("A"&(ROWS(OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1))))="W","L","W")))+1&":A"&ROWS(OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1))))

THIS IS AN ARRAY FORMULA

Probably too late to try since you have something that works, but I couldn't get my head around Tony's formula, so was determined to try something...

Best wishes!

David

Originally Posted by Tony Valko

Try this...

Data Range

	A	B	C
1	Won/Loss	------	Streak
2	L		7 W
3	W
4	W
5	W
6	L
7	W
8	L
9	L
10	W
11	W
12	W
13	W
14
15	W
16	W
17	W
18

This array formula** entered in C2:

=LOOKUP(1E100,FREQUENCY(IF(A2:A100=LOOKUP("zzz",A2:A100),ROW(A2:A100)),
IF(A2:A100<>LOOKUP("zzz",A2:A100),IF(A2:A100<>"",ROW(A2:A100)))))
&" "&LOOKUP("zzz",A2:A100)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hi Tony, I notice this is an older post, but hopefully you'll see this...The above array works great for what I need it to do, but is there any way to make it return W7 instead of 7 W (7"space"W)? I tried to manipulate it, but don't really understand how it works and couldn't make it happen.

Originally Posted by AliGW

Try this:

=LOOKUP("zzz",A2:A100)&LOOKUP(1E100,FREQUENCY(IF(A2:A100=LOOKUP("zzz",A2:A100),ROW(A2:A100)),IF(A2:A100<>LOOKUP("zzz",A2:A100),IF(A2:A100<>"",ROW(A2:A100)))))

Works perfectly, thank you very much! I'm new to the forum and didn't realize I should have started a new thread for this. Will do next time!