Forum Discussion
Solved
Average function Altered when dragging down / drop down
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!
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))
12 Replies
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.
The file, for the record.
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))- 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,gerald_gl01 Sorry, but how does this relate to your original post? It seems you are referring to minutes now.
Riny_van_Eekelenit worked thanks to the OFFSET function that you provided! Thanks!
