英文:
Using AVERAGE with ARRAYFORMULA
问题
我想使用这个等式的ARRAYFORMULA版本:
=IFERROR(((AVERAGE(SPLIT(C3, CHAR(10)))) + (AVERAGE(SPLIT(D3, CHAR(10)))) + (AVERAGE(SPLIT(E3, CHAR(10))))) / 3)
根据我所了解的阅读材料,AVERAGE函数在ARRAYFORMULA中似乎效果不佳?
这里是一个快速输入和期望输出的示例,使用上述公式在F列中拖动工作正常。但我想使用ARRAYFORMULA来完成这个任务。或者也许可以提供一个BYROW的解决方案,因为我想更好地理解如何使用它。
感谢您能提供的任何帮助。
编辑
我是否也可以选择以这种方式显示平均值?
英文:
I'd like to use the ARRAYFORMULA version of this equation:
=IFERROR(((AVERAGE(SPLIT(C3, CHAR(10)))) + (AVERAGE(SPLIT(D3, CHAR(10)))) + (AVERAGE(SPLIT(E3, CHAR(10))))) / 3)
From the reading I have done, it seems AVERAGE does not work well with ARRAYFORMULA?
Here is a quick image of inputs and expected outputs working fine with the formula above being dragged down in column F. But I'd like to do this with ARRAYFORMULA instead. Or perhaps BYROW solution as I'd like to get a better understanding of how to use it.
Appreciate any help I can get.
EDIT
Could I also be provided with the option to display the average in this way instead?
答案1
得分: 2
MAP()
函数可以在你的情况下起作用。尝试-
=MAP(C1:C2,D1:D2,E1:E2,
LAMBDA(x,y,z,AVERAGE(
AVERAGE(SPLIT(x,CHAR(10))),
AVERAGE(SPLIT(y,CHAR(10))),
AVERAGE(SPLIT(z,CHAR(10)))
))))
要引用整列作为输入范围,使用-
=MAP(
C1:INDEX(C1:C,COUNTA(C1:C)),
D1:INDEX(D1:D,COUNTA(C1:C)),
E1:INDEX(E1:E,COUNTA(C1:C)),
LAMBDA(x,y,z,AVERAGE(
AVERAGE(SPLIT(x,CHAR(10))),
AVERAGE(SPLIT(y,CHAR(10))),
AVERAGE(SPLIT(z,CHAR(10)))
))))
英文:
MAP()
function can work in your case. Try-
=MAP(C1:C2,D1:D2,E1:E2,
LAMBDA(x,y,z,AVERAGE(
AVERAGE(SPLIT(x,CHAR(10))),
AVERAGE(SPLIT(y,CHAR(10))),
AVERAGE(SPLIT(z,CHAR(10)))
)))
To refer full columns as input ranges, use-
=MAP(
C1:INDEX(C1:C,COUNTA(C1:C)),
D1:INDEX(D1:D,COUNTA(C1:C)),
E1:INDEX(E1:E,COUNTA(C1:C)),
LAMBDA(x,y,z,AVERAGE(
AVERAGE(SPLIT(x,CHAR(10))),
AVERAGE(SPLIT(y,CHAR(10))),
AVERAGE(SPLIT(z,CHAR(10)))
)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论