我修改了我在这里找到的解决方案。计算Excel中的移动平均线,但我我们努力进一步,找到最后4个值中最低(SMALL)3的平均值。
I modified a solution I found here Calculate Moving Average in Excel, but I'm struggling to take it one step further and find the average of the lowest (SMALL) 3 of the last 4 values.
现在我在那里找到的方程式最后四个值(在我的情况下是高尔夫分数),并且可以通过除以4创建平均值,但是我希望它能够在最后四个非空白单元格中取平均值三个最低值(最后四个意思是更大的列数)
The equation I have in there now finds the last four values (in my case golf scores) and can create an average by dividing by 4, but I'd like for it to take an average of the three lowest values in the last four non blank cells (last four meaning larger column number).
推荐答案
稍微简化的版本 - 试试这个数组公式。确保使用 Ctrl Shift 输入:
A slightly simplified version - Give this array formula a try. Be sure to confirm it with CtrlShiftEnter:
=SUM(SMALL(INDEX($F5:AH5,MATCH(TRUE,COLUMN($F5:AH5)=LARGE(NOT(ISBLANK($F5:AH5))*COLUMN($F5:AH5),4),0)):AH5,{1,2,3}))/3更多推荐
最近4个最低值的平均值
发布评论