Averaging data for multiple rows

[RICKDM]

I have data in column D beginning in row 2. I want a table that averages every 5 rows of data. So I want the first row of my table to be the average of rows 2-6, the second row to be the average of rows 7-11, and so on. I will be continually adding data to my spreadsheet that will need to be averaged. Is there a way to do this?

Thanks,
Rick

 

[circledchicken]

Hi,

The INDIRECT function should help. This article is very useful - http://www.cpearson.com/excel/indirect.htm.

Try the following in cell E2 and drag down as necessary:
=AVERAGE(INDIRECT("D"&5*ROW()-8&":D"&5*ROW()-4))

This will average cells in groups of five i.e.

• E2 = Average(D2:D6)
• E3 = Average(D7:D11)
• E4 = Average(D12:D16)
  etc

 

[RICKDM]

Thank you, does it matter whether the summary table starts ini E2, or can it be anywhere?

 

[T. Valko]

I have data in column D beginning in row 2. I want a table that averages every 5 rows of data. So I want the first row of my table to be the average of rows 2-6, the second row to be the average of rows 7-11, and so on. I will be continually adding data to my spreadsheet that will need to be averaged. Is there a way to do this?

Thanks,
Rick

One way...

Book1
	D	E	F
2	84	_	67
3	80	_	66.2
4	17	_	53.6
5	62	_	55.8
6	92	_	_
7	42	_	_
8	83	_	_
9	85	_	_
10	98	_	_
11	23	_	_
12	23	_	_
13	76	_	_
14	63	_	_
15	65	_	_
16	41	_	_
17	82	_	_
18	9	_	_
19	38	_	_
20	94	_	_
Sheet1

This formula entered in F2 and copied down:

=AVERAGE(OFFSET(D$2,(ROWS(F$2:F2)-1)*5,,5))

 

[circledchicken]

One way...

Sheet1

<table style="font-family: Verdana,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 37px;"><col style="width: 37px;"><col style="width: 37px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>D</td><td>E</td><td>F</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="text-align: center; border: 1px solid rgb(0, 0, 0);">84</td><td style="color: rgb(255, 255, 255); text-align: center; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: center; border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">67</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">80</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">66.2</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">17</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">53.6</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">62</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">55.8</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">92</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">42</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">83</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">85</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">98</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">23</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">23</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">76</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">63</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">65</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">41</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">82</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">9</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">38</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td><td style="text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">94</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td><td style="color: rgb(255, 255, 255); text-align: center; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-width: 1px; border-color: rgb(0, 0, 0);">_</td></tr></tbody></table>


This formula entered in F2 and copied down:

=AVERAGE(OFFSET(D$2,(ROWS(F$2:F2)-1)*5,,5))

Nice, I like this much better!

 

[RICKDM]

Thank you, that helped alot. Now, I want to create another column in my summary table that will average the rows in groups of five as before, except this time, only averageing every 4th group of five. So my first row (say, U2) will average cells D2-D6, U3 will average cells D22-D26, U4 will average cells D42-D46, and so on. I want to do this for V2 (averaging D6-D11, etc), W2 (averaging cells D12-D16, etc.) and X2 (averaging cells D17-D21, etc.)

 

[T. Valko]

Nice, I like this much better!

Thanks!

 

[T. Valko]

Thank you, that helped alot. Now, I want to create another column in my summary table that will average the rows in groups of five as before, except this time, only averageing every 4th group of five. So my first row (say, U2) will average cells D2-D6, U3 will average cells D22-D26, U4 will average cells D42-D46, and so on. I want to do this for V2 (averaging D6-D11, etc), W2 (averaging cells D12-D16, etc.) and X2 (averaging cells D17-D21, etc.)

Try this in U2:

=AVERAGE(OFFSET(D$2,(ROWS(U$2:U2)-1)*20,,5))

For the other columns all you need to do is add 5 to the offset reference:

V2: =AVERAGE(OFFSET(D$7,(ROWS(V$2:V2)-1)*20,,5))
W2: =AVERAGE(OFFSET(D$12,(ROWS(W$2:W2)-1)*20,,5))