Forum Discussion
Average function Altered when dragging down / drop down
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))
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.
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.
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.
Riny_van_Eekelen It worked thanks to the ROW( ) function that you provided! Thanks!!! :high_five_emoji:
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.
