Win/Loss CURRENT streak formula for standings

[sciartilli]

Hi- I have an easy request, just can't figure out a simple formula. I would like to create a CURRENT win/loss streak columns just like if you were looking at baseball/football standings.

I attached the spread sheet I am working on:

--"Standings" tab is where I want to see the data in the "STRK" column.
--"master schedule" is where the data will be pulled from. Column I represents the wins & column K represents the loses. I filled out some wins/loses just to have the data for trail purposes.

Ideally I would love to have to W# L# just like standing if you but I would take -3 or 4 for to represent W or L (if that makes sense)

thank you in advance for the help

Jason

[davsth]

Not such a simple formula!

I decided to find the row of the last win and the row of the last loss

last win k3
=SUMPRODUCT(MAX(ROW('Master Schedule'!$I$4:$I$59)*('Master Schedule'!$I$4:$I$59=$I3)))

last loss
=SUMPRODUCT(MAX(ROW('Master Schedule'!$I$4:$I$59)*('Master Schedule'!$K$4:$K$59=$I3)))

which ever is largest is the last result. so we know if the streak is wins or losses

so in the above k3 is 31 and k4 is 35, so the last result is a loss.

we need to find the losses where the team is the team of interest and the row number is > the row number of the last win

in g3 the result you want in the table
=IF(K3>L3,SUMPRODUCT((ROW('Master Schedule'!$I$4:$I$59)>L3)*('Master Schedule'!$I$4:$I$59=$I3))&"W",SUMPRODUCT((ROW('Master Schedule'!$I$4:$I$59)>K3)*('Master Schedule'!$K$4:$K$59=$I3))&"L")

I hope that makes sense