如何在Excel中查找每六个单元格的平均值 [英] How to find the average of every six cells in Excel

问题描述

这是一个相对常见的问题，所以我不想因提出之前提出的问题而被否决.我将在我逐步使用StackOver Flow和其他资源回答这个问题的步骤时进行解释，以便您可以看到我已尝试解决该问题而不解决问题.

This is a relatively common question so I don't want to be voted down for asking something that has been asked before. I will explain as I go along the steps I took to answer this question using StackOver Flow and other sources so that you can see that I have made attempts to solve it without solving a question.

我有一组值，如下所示:

I have a set of values as below:

 O       P    Q      "R"   Z
6307    586 92.07   1.34    
3578    195 94.83   6.00    
3147    234 93.08   4.29    
3852    227 94.43   15.00   
3843    171 95.74   5.10    
3511    179 95.15   7.18    
6446    648 90.87   1.44    
4501    414 91.58   0.38    
3435    212 94.19   6.23    

我想获取"R"行中前六个值的平均值，然后将该平均值放在Z的第六行中的第六列中，如下所示:

I want to take the average of the first six values in row "R" and then put that average in the sixth column in the sixth row of Z as such:

 O       P    Q      "R"   Z
 6307   586 92.07   1.34    
3578    195 94.83   6.00    
3147    234 93.08   4.29    
3852    227 94.43   15.00   
3843    171 95.74   5.10    
3511    179 95.15   7.18 6.49
6446    648 90.87   1.44    
4501    414 91.58   0.38    
3435    212 94.19   6.23        
414     414 91.58   3.49    
212     212 94.19   11.78   
231     231 93.44   -1.59 3.6   
191     191 94.59   2.68    
176     176 91.45   .75 
707     707 91.96   2.68    
792     420 90.95   0.75    
598     598 92.15   7.45    
763     763 90.66   -4.02   
652     652 91.01   3.75    
858     445 58.43   2.30  2.30

我利用了我获得的以下公式

I have utilized the following formula I obtained

=AVERAGE(OFFSET(R1510,COUNTA(R:R)-6,0,6,1))

但是我收到的答案与通过简单地将之前六个单元格的平均值取这样得到的答案不同:

but I received an answer that was different from what I obtained by simply taking the average of the six previous cells as such:

=AVERAGE(R1505:R1510)

然后，我尝试了Stack OverFlow中的以下代码( excel平均每10行)与我想要的切向相似的对话

I then tried the following code from a Stack OverFlow (excel averaging every 10 rows) conversation that was tangentially similar to what I wanted

=AVERAGE(INDEX(R:R,1+6*(ROW()-ROW($B$1))):INDEX(R:R,10*(ROW()-  ROW($B$1)+1)))

但是我无法得到类似于死记硬背的答案

but I was unable to get an answer that resembled what I got from taking a rote

==AVERAGE(R1517:R1522)

我还在下面找到了另一种方法，但是无法准确地更改编码(例如，从F3到R1510)

I also found another approach in the following but was unable to accurately change the coding (F3 to R1510, for example)

=AVERAGE(OFFSET(F3,COUNTA($R$1510:$R$1517)-1,,-6,))

这样做会给我一个明显为正数的数据负数.是-6.95.

Doing so gave me a negative number for a clearly positive set of data. It was -6.95.

推荐答案

将此内容放入Z1并复制下来:

Put this in Z1 and copy down:

=IF(MOD(ROW(),6)=0,AVERAGE(INDEX(R:R,ROW()-5):INDEX(R:R,ROW())),"")

这篇关于如何在Excel中查找每六个单元格的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！