Calculate winning or losing streak while ignoring draw

Hi guys,

In my excel, i tabulated the results of a soccer team, and its Win-Lose-Draw result can be seen in Column H.
The current results are WWDLL.
In Cell K2 and L2, i am trying to calculate the latest W/L streak that the team is experiencing.
The array formula i used, which i found by googling is:

For win streak
=MAX(0,MAX((H2:H40="W")*ROW(H2:H40))-MAX((H2:H40="L")*ROW(H2:H40)))
This gave a result of 0, which is correct since last game result is a "L"

For lose streak
=MAX(0,MAX((H2:H40="L")*ROW(H2:H40))-MAX((H2:H40="W")*ROW(H2:H40)))
This gave a result of 3, which is WRONG, as i have only 2 L

Is there anyway to calculate W or L, while treating D as "invisible"?
The formula i am looking for is to ignore D. This means if there is a result of WLDLL, the Lose streak should show 3.

Thanks in advance, i tried googling and looking through the forum, there are some similar posts regarding streaks, but they are all different from this case.

Appreciate your help.

Originally Posted by FlameRetired

Hi stensten. Welcome to the forum.

Try array entering this in K2. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Use the same logic for the other cells.

Hi FlameRetired,

Thanks so much for your help! I entered the array formula and i got the following result:

For Win streak: 2
For Lose streak: 2

I am sorry if i did not make my post clear, i am looking for the latest streak while ignoring "D".

So my desired result is

Win streak:0
Lose streak: 2

As the result (from first to last) is WWDLL. The latest streak for this team is 2 Losses.

If next game is a W, and result becomes WWDLLW, the latest streak i am looking for is 1 Win and Losses will be 0.

Hope you can help me further with this, sorry for the confusion!

Hi FlameRetired,

Sorry for the late reply, due to time difference.

Yes the dates in column A are always in ascending order.

Your new formula solved part of the problem. Current result is WWDLL

So your formula correctly pointed out the latest streak as 2.

However if i add on to the result, assuming it becomes WWDLLDLL, my desired result is 4 losses, ignoring the Ds in between. Your formula gives the answer of 2, i guess because it is interrupted by the D. Any way to overcome this?

I tried another way. which is to make the D result a blank instead. So the cells will become WW LL.
In another column, i use a formula to remove the blanks. Result will become WWLL.
Then i can use calculate the streak.
However, i am still struggling to comprehend the formula which removes the blanks. the formula i found on the internet is (assuming data is in A2 to A9)
=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISTEXT($A$2:$A$10),ROW($A$1:$A$9),""),ROW(A1))),"")

Thanks for the help, really appreciate it.

Originally Posted by FlameRetired

This changes things. Will have to do a re-think.

Hi FlameRetired,

Thanks, you have been a great help.

I managed to use formula to set "D" to blank (displaying as 0) in Column H. In Column J, have a formula to remove the blanks.
For Column I, same thing. For result that is "Pushed", i set it to blank. And in Column K, the formula removes the blanks in between.

However, now there seems to be a problem with the formula in N2, O2, P2 and Q2.

Purely looking at Column J and Column K, we can see there are 4 Ws and 4 Under. Why does the formula in N2 and Q2 gives result of 5??

Thanks,
stensten