英文:
Google Sheets - Add value of cells until it reaches X or Y values, then return answer for X or Y
问题
我正在使用Google表格,尝试弄清楚如何编写难以解释的逻辑。
第1列开始从第1行到第5行编译总和计数;如果总和 >= X,则应输出"成功";如果总和 <= Y,则应输出"失败"。
例如,从单元格第1行、第01列开始,直到总和达到 >= "4"。如果达到4,总和应停止并输出"成功",如果总和达到 <= -1,则应停止并输出"失败"。
我想将此应用于数据表中的许多不同行。希望你能理解我的意思。谢谢。
第01列
1 1
2 2
3 -3
4 4
5 -2
英文:
I'm using Google Sheets and I'm trying to figure out how to write the logic for something difficult to explain.
Col 1 starts to compile a sum count from rows 1-5; If the sum is >= X it should output "Success"; If the sum <= Y it should output value "Fail".
For example, sum cells Row 1, Col 01 -> Row 5, Col 1 until sum reaches >= "4". If it reaches 4, the summing should stop and output "Success" AND if summing results in reaching <= -1 it should stop and output "Fail".
I'd like to apply this to many different rows within the data table. Hopefully you can understand where I'm going with this. Thanks.
Col 01
1 1
2 2
3 -3
4 4
5 -2
答案1
得分: 1
SCAN允许你运行累积和。例如:
=SCAN(0, TOCOL(A2:A,1), LAMBDA(a, v, a+v))
它返回累积总和的值,因为它将先前的值(a)与新值(v)相加。现在你只需要添加IF语句:
=SCAN(0, TOCOL(A2:A,1), LAMBDA(a, v, IF(a+v>=4, "成功", IF(a+v<=-1, "失败", a+v))))
现在,在成功或失败之后,累积总和将停止,因为不能将其添加到字符串中。这就是为什么你可以使用IFERROR来清除这些值,并且如果你想要精确缩小结果到那些单元格,还可以使用TOCOL:
=TOCOL(IFERROR(SCAN(0, TOCOL(A2:A,1), LAMBDA(a, v, IF(a+v>=4, "成功", IF(a+v<=-1, "失败", a+v))))), 1)
所以,只需要一个公式在B2中,你可以得到这个结果:
英文:
SCAN lets you run cumulative sums. For example:
=SCAN(0, TOCOL(A2:A,1),LAMBDA(a,v, a+v ))
It returns the value of the cumulative sum, because it sums the previous value (a) with the new one (v). Now you only need to add the IF statements:
=SCAN(0,TOCOL(A2:A,1),LAMBDA(a,v,
IF(a+v>=4,"Success",IF(a+v<=-1,"Fail",a+v))))
Now, after Success or Fail, the cumulative sum will stop since it's not possible to add to a string. That's why you can add IFERROR to empty those values, and TOCOL optionally if you want to narrow the results exactly to those cells:
=TOCOL(IFERROR(SCAN(0,TOCOL(A2:A,1),LAMBDA(a,v,IF(a+v>=4,"Success",IF(a+v<=-1,"Fail",a+v))))),1)
So, with only one formula in B2 you get this result:
答案2
得分: 0
这绝对是一个冗长的方法,但这是我的答案:
在A列,您放置所有数据。
在B1中,输入以下内容:(根据数据范围更改A1:A5)
=MAP(A1:A5, LAMBDA(val, IF(CELL("row", val)=1,A1,SUM(INDIRECT("A1:"&ADDRESS(IF(CELL("row", val) - 1<1, 1, CELL("row", val) - 1),CELL("col", val),4))))))
这基本上在B列中放入了列A中单元格上方所有内容的总和。
在下一部分中,假设单元格D2是最小值,E2是最大值。
在C列中,C1将是:
=IF(B1>=E2,"SUCCESS",IF(B1<=D2,"FAIL",""))
这将在满足条件时将其设置为SUCCESS和FAIL。
在列C的所有后续行中,输入以下内容:
=IF(INDEX(A:Z,ROW()-1,COLUMN())="",IF(INDEX(A:Z,ROW(), COLUMN()-1)>=E2,"SUCCESS",IF(INDEX(A:Z,ROW(), COLUMN()-1)<=D2,"FAIL",""))," ")
这与第一列相同,但如果上方的单元格已经这样做,就不会将单元格设置为成功或失败。
可选地,在不同的单元格中,您可以输入:(根据数据范围更改C1:C5)
=SWITCH(SUM(MAP(C1:C5,LAMBDA(val, SWITCH(val, "SUCCESS", 1, "FAIL", -1, "", 0, " ", 0, 2)))),1,"SUCCESS", 0, "", -1,"FAIL", "ERROR")
英文:
This is definitely the long way to do it, but here is my answer:
In column A, you put all your data.
In B1, put in this: (change A1:A5 based of range of data)
=MAP(A1:A5, LAMBDA(val, IF(CELL("row", val)=1,A1,SUM(INDIRECT("A1:"&ADDRESS(IF(CELL("row", val) - 1<1, 1, CELL("row", val) - 1),CELL("col", val),4))))))
This essentially puts in column B the sum of everything above the cell in column A.
In this next part, say that cell D2 is the minimum value, and E2 is the maximum value.
In column C, C1 is going to be:
=IF(B1>=E2,"SUCCESS",IF(B1<=D2,"FAIL",""))
this sets it to SUCCESS and FAIL if the conditions are met.
On all the subsequent rows in column C, enter in:
=IF(INDEX(A:Z,ROW()-1,COLUMN())="",IF(INDEX(A:Z,ROW(), COLUMN()-1)>=E2,"SUCCESS",IF(INDEX(A:Z,ROW(), COLUMN()-1)<=D2,"FAIL",""))," ")
This does the same as the first column, but does not set the cell to success or fail if one above has already done so.
Optionally, in a different cell you can put: (change C1:C5 based on range of data)
=SWITCH(SUM(MAP(C1:C5,LAMBDA(val, SWITCH(val, "SUCCESS", 1, "FAIL", -1, "", 0, " ", 0, 2)))),1,"SUCCESS", 0, "", -1,"FAIL", "ERROR")
This tells you if any of them at all got labeled as success or fail.
Here the final product:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论