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 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.
- 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.
Riny_van_Eekelenit worked thanks to the OFFSET function that you provided! Thanks!
