Hi, I've been searching for an answer to this for a while but can't work it out, so I'm calling for help!
(BTW, I'm using Google Sheets)
I've a list of football results, and I'm trying to calculate the longest unbeaten run. Column A shows the result (Won, Lost, Drawn). I'd like column B to increase by 1, for every row until a "Lost" is reached. The "Lost" resets the counter to 0.
Here is an example of what the outcome should be.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Won[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Won[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Drawn[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Drawn[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lost[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Lost[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Won[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Lost[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Won[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Drawn[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Thank you so much in advance!
(BTW, I'm using Google Sheets)
I've a list of football results, and I'm trying to calculate the longest unbeaten run. Column A shows the result (Won, Lost, Drawn). I'd like column B to increase by 1, for every row until a "Lost" is reached. The "Lost" resets the counter to 0.
Here is an example of what the outcome should be.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Won[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Won[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Drawn[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Drawn[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Lost[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Lost[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Won[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Lost[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Won[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Drawn[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Thank you so much in advance!