英文:
How to Count Winning Streak when Player Does Not Play?
问题
I am trying to count a win streak in google sheets by counting the number of ones in a row (1).
Here is the current formula that I am using:
=sortn(frequency(if((M2:M=1)*len(M2:M),row(M2:M)),if((M2:M=1)*len(M2:M),,row(M2:M))),1,0,1,0)
It works great, but unfortunately the win steak ends if a player does not play (DNP). So if one of those rows does not have a 1 in the Win, and it is blank, then the win streak is over.
I would like the win streak to include blank rows too if possible. Any suggestions on how I can update this formula, so my win steak does not end with a blank value?
Thanks in advance for any help that you may offer. =)
Just to add a litte more clarity: Right now if I have ten "1"s in a row, but the fifth row does not have any data (DNP) then my win streak would only be 5.
This is the output that I would like below:
Here is an example of the recent output suggested:
英文:
I am trying to count a win streak in google sheets by counting the number of ones in a row (1).
Here is the current formula that I am using:
=sortn(frequency(if((M2:M=1)*len(M2:M),row(M2:M)),if((M2:M=1)*len(M2:M),,row(M2:M))),1,0,1,0)
It works great, but unfortunately the win steak ends if a player does not play (DNP). So if one of those rows does not have a 1 in the Win, and it is blank, then the win streak is over.
I would like the win streak to include blank rows too if possible. Any suggestions on how I can update this formula, so my win steak does not end with a blank value?
Thanks in advance for any help that you may offer. =)
Just to add a litte more clarity: Right now if I have ten "1"s in a row, but the fifth row does not have any data (DNP) then my win streak would only be 5.
This is the output that I would like below:
Here is an example of the recent output suggested:
答案1
得分: 2
从您的示例中,我已经发现如果您只是将其留空,就没有必要使用其他列。而且使用公式来跟踪连胜连负会变得更加困难。我建议仅使用1列来表示胜利/失败状态,其中1表示胜利,0表示失败,并使用以下公式:
=INDEX(COLUMNS(SPLIT(FLATTEN(SPLIT(REGEXREPLACE(TRIM(QUERY(A2:A,,9^9)), " DNP ", " "), "0", )), " ")))
结果:
来自Tedinoz发布的评论,链接到:
我修改了被接受的答案,将DNP替换为空,以便继续跟踪胜利连胜。
英文:
Suggestion:
From your sample, I have figured that it was unnecessary to use other columns if you will just leave it blank. And also it makes it much more difficult to track the streaks using formulas. I suggest using only 1 column for the Win/Lose status where 1 = Win and 0 = Loss, and use the following formula:
Try:
=INDEX(COLUMNS(SPLIT(FLATTEN(SPLIT(REGEXREPLACE(TRIM(QUERY(A2:A,,9^9)), " DNP ", " " ), "0", )), " ")))
From the posted comment of Tedinoz linking to :
How to count streaks of non-zero numbers on one column.
I have modified the accepted answer to replace the DNP with blank so it will continue to track the win streak.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论