I'm having trouble figuring out the formula for the yellow fields I highlighted in the excel sheet. I'd like to calculate the highlighted area:
我在查找excel表中突出显示的黄色字段的公式时遇到了麻烦。我想计算突出显示的区域:
- Best day of the week to play
- Worst day of the week to play
- Longest win streak
一周中最好的一天
本周最糟糕的一天玩
最长连胜
2 个解决方案
#1
The below descibes how you can do it for 'Carter', you can then change the formula to return values for "Pascal'.
下面描述了如何为'Carter'做到这一点,然后你可以改变公式来返回“Pascal”的值。
1. Best day of the week to play I would look into using an index match on the 'Wins by day of the week' section, returning the data in column H, selecting the row based on the MAX value from I16:I22. You'll want to consider what value you want returned if two days have the same no. of wins. You'll need to adjust the ranges, I demo'd it on some random cells on my worksheet:
1.播放一周的最佳日期我会研究在'星期几的胜利'部分使用索引匹配,返回H列中的数据,根据I16中的MAX值选择行:I22。如果两天有相同的no,你会想要考虑你想要返回什么值。胜利您需要调整范围,我在工作表上的一些随机单元格上进行了演示:
=INDEX(B5:C9,MATCH(MAX(C5:C9),C5:C9,0),1)
2. Worst day of the week to play As with the best day, although select the value based on the MIN value in I16:I22. Formula would be as above but change MAX to MIN
2.最好的一周播放与最好的一天一样,虽然根据I16中的MIN值选择值:I22。公式如上,但将MAX更改为MIN
3. Longest win streak This will be easier to calculate if you include a streak column for each player, and then you can use a MAX value on the entire column. In the column increment the cell above by 1 if it is a win for that player. If you go down the additional column route you'd need to just have a formula such as:
3.最长连胜如果你为每个玩家添加一个条纹列,那么这将更容易计算,然后你可以在整个列上使用MAX值。如果是该玩家的胜利,则在该列中将单元格增加1。如果你沿着额外的列路线走下去,你需要有一个公式,例如:
=MAX(B:B)
If column B had the winning streak count, you just need to have in the cells the formula in B2, looking to the winning name (Pascal in my instance, you'll need to adjust the cell reference for the column as I can't see your image on this edit page at the moment):
如果列B具有连胜计数,您只需要在单元格中包含B2中的公式,查找获胜名称(在我的实例中为Pascal,您需要调整列的单元格引用,因为我不能此时在此编辑页面上看到您的图片):
=IF(D2="Pascal",B1+1,0)
#2
For the Longest Win Streak:
对于最长的胜利条纹:
=ArrayFormula(MAX(FREQUENCY(IF(E:E="Carter",ROW(E:E)),IF(E:E<>"Carter",ROW(E:E)))))
This was the formula that actually worked.
这是实际工作的公式。
#1
The below descibes how you can do it for 'Carter', you can then change the formula to return values for "Pascal'.
下面描述了如何为'Carter'做到这一点,然后你可以改变公式来返回“Pascal”的值。
1. Best day of the week to play I would look into using an index match on the 'Wins by day of the week' section, returning the data in column H, selecting the row based on the MAX value from I16:I22. You'll want to consider what value you want returned if two days have the same no. of wins. You'll need to adjust the ranges, I demo'd it on some random cells on my worksheet:
1.播放一周的最佳日期我会研究在'星期几的胜利'部分使用索引匹配,返回H列中的数据,根据I16中的MAX值选择行:I22。如果两天有相同的no,你会想要考虑你想要返回什么值。胜利您需要调整范围,我在工作表上的一些随机单元格上进行了演示:
=INDEX(B5:C9,MATCH(MAX(C5:C9),C5:C9,0),1)
2. Worst day of the week to play As with the best day, although select the value based on the MIN value in I16:I22. Formula would be as above but change MAX to MIN
2.最好的一周播放与最好的一天一样,虽然根据I16中的MIN值选择值:I22。公式如上,但将MAX更改为MIN
3. Longest win streak This will be easier to calculate if you include a streak column for each player, and then you can use a MAX value on the entire column. In the column increment the cell above by 1 if it is a win for that player. If you go down the additional column route you'd need to just have a formula such as:
3.最长连胜如果你为每个玩家添加一个条纹列,那么这将更容易计算,然后你可以在整个列上使用MAX值。如果是该玩家的胜利,则在该列中将单元格增加1。如果你沿着额外的列路线走下去,你需要有一个公式,例如:
=MAX(B:B)
If column B had the winning streak count, you just need to have in the cells the formula in B2, looking to the winning name (Pascal in my instance, you'll need to adjust the cell reference for the column as I can't see your image on this edit page at the moment):
如果列B具有连胜计数,您只需要在单元格中包含B2中的公式,查找获胜名称(在我的实例中为Pascal,您需要调整列的单元格引用,因为我不能此时在此编辑页面上看到您的图片):
=IF(D2="Pascal",B1+1,0)
#2
For the Longest Win Streak:
对于最长的胜利条纹:
=ArrayFormula(MAX(FREQUENCY(IF(E:E="Carter",ROW(E:E)),IF(E:E<>"Carter",ROW(E:E)))))
This was the formula that actually worked.
这是实际工作的公式。