Counting given amount of consecutives

[Marvo]

Hi Again, I've constructed a formula

=XLOOKUP("W",K:K,B:B,,0,-1)

That works well, it finds the most recent time there was a "W" in a column.

How could I change that formula so that it would look for a given amount of consecutive "W"s please?

I'm thinking maybe of having it refer to a cell $F$5579 with a number in it, then look for that amount of "W"'s.

So if I put a 3 in the reference cell, the formula would then look for the most recent time there were 3 consecutive "W"'s in column K.

Would that be the way to go?

As always any help appreciated and if there's a better way of achieving the desired result I'm happy to be educated.


/?

 

[Marvo]

XL2BB isn't in the add-ins lost.

 

[Peter_SSs]

XL2BB isn't in the add-ins lost.

If you are referring to the yellow below, then you have probably not read the green carefully as those first set of steps are only if you already have a previous version installed.

If you don't already have a previous version installed, move on to the next solid bullet point.

 

[Marvo]

Well I've downloaded and added it to a workbook, followed instructions until it says click on XL2BB in the ribbon but it isn't shown on the ribbon.

 

[Peter_SSs]

I've downloaded and added it to a workbook,

Where does it say to do that?

until it says click on XL2BB in the ribbon but it isn't shown on the ribbon.

Where does it say to do that? Do you mean this instead?

Click on the custom ribbon tab called Xl2bb

..meaning this, which is above the ribbon not in the ribbon

 

[Marvo]

Yes, that's what's missing.

 

[Marvo]

I open a new book. I go to the developer tab, click on excel add-ins, X12BB is listed. That's as far as I get, it doesn't show above the ribbon.

 

[Marvo]

So, shown in the image, you can enter any number in Columns C, F, I, O & R and the adjacent box will exhibit the most recent date when that consecutive run was achieved.
As an example I've placed a 5 in C21 and that has returned a date of 6/02/2016 which is the start of the most recent consecutive 5 game run of matches won.
The formula in D21 is =IF(C21="","",XLOOKUP(C21,ALL!$AH$2:$AH$5558,ALL!$B$2:$B$5558,,0,-1))

The formula in AH2 is =IF(K5481="W",1+AH5482,0) copied down.

It all works a treat, I may in future put a hyperlink to D21 so that you can go straight to the result. The only downside? Its doubled the size of the workbook.

I hope this might help someone else in future, I was unable to find a solution o the question anywhere.

Many thanks to all those that took an interest.

 

[Peter_SSs]

Yes, that's what's missing.

Wondering if the Xl2bb menu item ever appeared above the ribbon and has since disappeared?
If so, or in any case, it might be worth having a good look through this thread as well.

 

[Marvo]

Thank you Peter. I was contacted by message by another member at your behest who tried to help, gave me instructions which I followed to the letter, but when I got to the step where you put the add-in file into the XLSTART folder, that folder simply doesn't exist. There was one called STARTUP but that's empty and it wont let you add to it. I was going to report back to the chap but he had closed the chat down and there was no way for me to contact him. All roads I'm afraid lead to a dead end.

 

[Peter_SSs]

Can you show me a picture of what happens when you perform this step from the instructions?

Here is my picture