英文:
Array Formula that drops two lowest values and then averages a total
问题
我需要一些帮助来创建一个数组公式，以帮助整理学生的成绩。在进行了六次测试后，我想删除每个学生的两个最低分，然后求剩下四个的平均值。数组公式将从附上的链接中的H2开始。任何帮助将不胜感激！
链接: https://docs.google.com/spreadsheets/d/1z-4hzwBK6YUuFncZsN2NhNF8eDnCEKJ9XLmKHnoJQQE/edit?usp=sharing
最接近的我能做到的是将四个最大值相加，然后求平均值，但我必须为每一行都这样做。我希望制作一个数组公式，这样我可以轻松地将其复制到每个学期不同的班级规模中。
英文:
I am in need of some help creating an array formula to help organize student grades. After six tests I would like to drop the two lowest scores for each student and then average the remaining four. The array formula would start in H2 in the attached link. Any help would be greatly appreciated!
https://docs.google.com/spreadsheets/d/1z-4hzwBK6YUuFncZsN2NhNF8eDnCEKJ9XLmKHnoJQQE/edit?usp=sharing
The closest I can get is summing the four largest amounts and then averaging them out but I have to do this for every line. I am hoping to make an array formula so I can easily copy this to different class sizes per semester.
答案1
得分: 1
你可以尝试：
=byrow(B2:G,lambda(Σ,if(offset(index(Σ,,1),,-1)="",,round(average(sortn(tocol(Σ),4,,1,0)),2))))
英文:
You may try:
=byrow(B2:G,lambda(Σ,if(offset(index(Σ,,1),,-1)="",,round(average(sortn(tocol(Σ),4,,1,0)),2))))
答案2
得分: 1
这个与RockShow并没有太大不同，但在测试数量变量方面增加了一个动态元素，因此在范围内可能会更灵活。如果不明显的话，将testsToDrop后面的2更改为其他数字将相应地调整输出。
=let(testsToDrop,2,byrow(filter(B2:G,A2:A<>""),lambda(aRow,(sum(arow)
-sum(ARRAYFORMULA(small(aRow,SEQUENCE(1,testsToDrop,1,1)))))/
(count(arow)-testsToDrop))))
英文:
This one isn't much different than RockShow, but it has a dynamic element to it for one variable of number of tests to drop so it might be a little more flexible along with the range. In case it's not obvious, changing the 2 after testsToDrop will adjust the output accordingly.
=let(testsToDrop,2,byrow(filter(B2:G,A2:A<>""),lambda(aRow,(sum(arow)
-sum(ARRAYFORMULA(small(aRow,SEQUENCE(1,testsToDrop,1,1)))))/
(count(arow)-testsToDrop))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论