How to find the average of every six cells in Exce

问题:

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    

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)

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)

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

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

回答1:

Put this in Z1 and copy down:

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