英文:
Is there a straightforward way to combine these two steps into one formula?
问题
在单元格A1:D5中有一些数据。
在单元格F1:F5中，我为每一行（从A到D列）计算某种几何平均值。这是中间步骤。
最后，在单元格F7中，我取前一步骤的值的中位数。
是否有一种方法可以跳过中间步骤，让单元格F7自行完成所有工作，而不必将上面的五个公式复制到它中？（我希望它保持灵活，这样它也可以用于更大的数据集。）
英文:
I have some data in cells A1:D5.
In cells F1:F5, I calculate some kind of geometric average for each row (column A through D). This is the intermediate step.
Finally, in cell F7, I take the median of the values of the previous step.
Is there a way to skip the intermediate step and have cell F7 do all the work by itself, without copying the five formulas above into it? (I want it to remain flexible, so it would also work for larger data sets.)
答案1
得分: 3
On Microsoft-365 you may utilize BYROW() function. Give a try on-
=MEDIAN(BYROW(A1:D5,LAMBDA(x,EXP(AVERAGE(LN(1+x)))-1)))
英文:
On Microsoft-365 you may utilize BYROW() function. Give a try on-
=MEDIAN(BYROW(A1:D5,LAMBDA(x,EXP(AVERAGE(LN(1+x)))-1)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论