Most recent consecutive streak not including "p"

**thehoorse** · 02-13-2013, 04:29 AM

Hello. I have been helped before here and hoping someone can rescue me again...

I'm looking for the formula which will count the current (most recent) streak of wins ("w") and losses ("l") while not allowing pushes ("p" - as in a draw) to break up the streak. In other words I would like for "p" (push) to be ignored. The result does not have to distinguish whether it is a win or loss. All I care about is the number of the current streak (regardless of whether it is a win or loss). Here are some examples...

W L W W L P L L W P W The result I am looking for is 2 (since the current streak is 2 wins while ignoring pushes)

L P L L W W L L P L L The result I am looking for here is 4

W L W L L W W W W L L The result is 2

W L W P L L P L P W W L P The result is 1 (since last consecutive streak of non push is a loss)

Appreciate any suggestions.

**Ace_XL** · 02-13-2013, 03:18 PM

See if the attached helps..

See column N

**thehoorse** · 02-13-2013, 06:11 PM

Thank you for the reply. It works, the only problem is that I will be adding results to the end of each row and I see that CONCATENATE has to list each cell in the formula. Is there any way to place the formula as a range that may continue to increase?

Thanks again.

**thehoorse** · 02-13-2013, 07:49 PM

Anyone have an answer or fix for this?

**benishiryo** · 02-13-2013, 09:24 PM

hi there. try this array formula:

Formula:

Please Login or Register  to view this content.

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it. if you have more columns, change all the "M". ideally, use a standard formula with the most column & it'll work for all your data.