在另一个公式中使用的单元格旁边的单元格的AVG

请尝试以下公式：

=SUMPRODUCT(O15:BM15,
--(MOD(COLUMN(N15:BL15)-COLUMN($N15),3)=0),
--(N15:BL15+O15:BM15/10^3+COLUMN(N15:BL15)/10^6>=
   LARGE(N15:BL15+O15:BM15/10^3+COLUMN(N15:BL15)/10^6,6))
   )/6

它是如何工作的？

SUMPRODUCT有3个参数-第一个是要求和的数组，接下来的2个参数返回的数组，0并1仅选择第一个数组中有趣的元素。

• MOD(COLUMN(N15:BL15)-COLUMN($N15),3)=0)

This part is included to avoid listing every single cell. If the score is in every third column of the input range, we can calculate column number relative to first column, and function MOD(column,3) returns: {1,0,0,1,0,0...}. So only every third column of input array will be included in sum.

• (N15:BL15+O15:BM15/10^3+COLUMN(N15:BL15)/10^6>= LARGE(N15:BL15+O15:BM15/10^3+COLUMN(N15:BL15)/10^6,6)

This part is to decide which 6 of the scores should be included in the final sum. The trickiest part is to decide what to do with ties. My approach is to take:

• if two scores are the same, take the one with higher number of bulleyes
• if it is still tied, take the one from first columns

This means that instead of N15 value we calculate:

N15+O15/10^3+COLUMN(N15)/10^6

With your sample data it evaluates to: 566.017014. First three decimal places is the number of bulleyes, next 3 is column number.

You can use the same formula to calculate average of top 6 scores by changing the first parameter:

=SUMPRODUCT(N15:BL15,
--(MOD(COLUMN(N15:BL15)-COLUMN($N15),3)=0),
--(N15:BL15+O15:BM15/10^3+COLUMN(N15:BL15)/10^6>=
   LARGE(N15:BL15+O15:BM15/10^3+COLUMN(N15:BL15)/10^6,6))
   )/6