SOLVED

Average function Altered when dragging down / drop down

New Contributor

Hi,

I am currently calculating averages every five cells:

 

=AVERAGE(B1:B5)
=AVERAGE(B6:B10)
=AVERAGE(B11:B15)

When I do dragg down / drop down the pattern changes. The arguments within the the AVERAGE function syntax do not folow the pattern of:

 

=AVERAGE(B16:B20)
=AVERAGE(B21:B25)
=AVERAGE(B26:B30)

Instead they follow the following pattern, and each time I do the drag down / drop down a different pattern appears, like the one below.
=AVERAGE(B4:B8)
=AVERAGE(B9:B13)

=AVERAGE(B14:B18)

 

Any help is highly appreciated. Thanks!

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@gerald_gl01 That's because you are actually copying three formulas down to the next three rows. The relative row references in the first formula B1:B5 are increased by 3, thus B4:B8. Similar for the other ones. B6:B10 becomes B9:B13 and B11:B15 becomes B14:B18.

 

You would need to use an OFFSET function that "calculates" the next range of five cells, based on the current row number.

 

Enter this in C1 and it should work:

=AVERAGE(OFFSET($B$1,(ROW()-1)*5,0,5))

 

@Riny_van_Eekelenit worked thanks to the OFFSET function that you provided! Thanks!