Average function Altered when dragging down / drop down

[gerald_gl01]

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 · Accepted 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))

[#3]

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

[#4]

Greetings,

What would be the four OFFSET functions that "calculate" the next range of fifteen (15), forty-five (45) ,sixty (60), and (1440) cells based on the current row number?

Thanks,

[#5]

@gerald_gl01 Sorry, but how does this relate to your original post? It seems you are referring to minutes now.

[#6]

I am trying to calculate new averages of (15), (45), (60, and (1440), I thought that by using the same function and changing the character "5" for the new average that I wanted would by enough, but when doing that the value that I am getting is not the correct one. Thanks.

[#7]

@gerald_gl01 OK, can you please show the formula that you are trying to use? A screenshot would help. Better even if you could upload or share a file demonstrating your intent.

[#8]

@gerald_gl01 

This most likely doesn't match the needs of the OP but, to widen the discussion, you do not need to drag formulas using 365, the entire concept of relative referencing is unnecessary.

= LET(
    blocks, WRAPROWS(data, 4),
    BYROW(blocks, LAMBDA(b, AVERAGE(b)))
  )

An advantage of using arrays to return results is that they can be absolute.  Amongst other things, this means that when you copy formulas to scratch space to work on them, the results do not depend upon the location.

[#9]

The file, for the record.

[#10]

@Riny_van_Eekelen; in column G, where I am calculating the average of three numbers using the OFFSET function, I have to change manually the rows in the OFFSET function. The rows in the OFFSET function are incrementing by 3 when I calculate new averages for the following three sales prices.

Functions that I am using:

=OFFSET(reference, rows, cols, [height], [width])

=AVERAGE(OFFSET(reference, rows, cols, [height], [width]))

Functions in the photos shown:

=AVERAGE(OFFSET($D$5,1,0,3,1))

=AVERAGE(OFFSET($D$5,4,0,3,1))

=AVERAGE(OFFSET($D$5,7,0,3,1))

=AVERAGE(OFFSET($D$5,10,0,3,1))

Etc.

Is there a way where I can put an incrementing counter for rows in the OFFSET function? That way I do not have to change the incrementing number manually. I tried using the ROW function, but did not have any luck using that.

Reference / Original Author from where I am creating this example: 

Excel Bonanza. (2018, September 20). Excel 2016 OFFSET function basics + Dynamic Ranges | 5 Examples. YouTube. https://www.youtube.com/watch?v=-5-kh-tq0AA

PS: In the reference they are not trying to do what I am doing by the way.

[#11]

@gerald_gl01 

You could introduce a helper range containing the pointers 1,4,7,10. and drag the formula down.  With 365, you could use an array but, then, so much more would be different.

[#12]

@gerald_gl01 Please see attached file where I entered working formulas in column N.

Column P contains the formula that I used to dynamically calculate the numbers 1, 4, 7 etc.

I then entered that code into the formulas in column N.

[#13]

@Riny_van_Eekelen It worked thanks to the ROW( ) function that you provided! Thanks!!! :high_five_emoji: