如何计算玩家不参与比赛时的连胜次数?

huangapple go评论60阅读模式
英文:

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", )), " ")))

Result:
如何计算玩家不参与比赛时的连胜次数?

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.

huangapple
  • 本文由 发表于 2023年6月1日 05:31:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/76377429.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定