使用ARRAYFORMULA的AVERAGE函数

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

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的解决方案,因为我想更好地理解如何使用它。

感谢您能提供的任何帮助。

编辑

我是否也可以选择以这种方式显示平均值?

使用ARRAYFORMULA的AVERAGE函数

英文:

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.

使用ARRAYFORMULA的AVERAGE函数

Appreciate any help I can get.

EDIT

Could I also be provided with the option to display the average in this way instead?

使用ARRAYFORMULA的AVERAGE函数

答案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)))
))))

使用ARRAYFORMULA的AVERAGE函数

英文:

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

使用ARRAYFORMULA的AVERAGE函数

huangapple
  • 本文由 发表于 2023年5月28日 13:26:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/76350070.html
匿名

发表评论

匿名网友

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

确定