Formula Increment with Cell Jumps
Solved
sara-87
Posted messages
79
Status
Member
-
sara-87 Posted messages 79 Status Member -
sara-87 Posted messages 79 Status Member -
Hello,
My question has probably already been asked, but I can't seem to find an answer.
I have a time data table with 35,066 rows and 14 columns, and I would like to calculate the daily average for each column by skipping 24 rows. How could I apply the initial formula, for example: =average(B2:B25) to jump directly to =average(B25:B50) for the next cell instead of =average(B4:B26)...
I've tried several manipulations with the OFFSET function, but I still can't find the right one.
If my request isn't very clear, here's a sample: https://www.cjoint.com/c/GEqq5tSglrD
Thank you in advance for your replies.
My question has probably already been asked, but I can't seem to find an answer.
I have a time data table with 35,066 rows and 14 columns, and I would like to calculate the daily average for each column by skipping 24 rows. How could I apply the initial formula, for example: =average(B2:B25) to jump directly to =average(B25:B50) for the next cell instead of =average(B4:B26)...
I've tried several manipulations with the OFFSET function, but I still can't find the right one.
If my request isn't very clear, here's a sample: https://www.cjoint.com/c/GEqq5tSglrD
Thank you in advance for your replies.
5 answers
Hello,
your formula was almost correct, it just needs a $. In B2:
eric
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to the thank you (yes, indeed, it can be done!!!), remember to set it to resolved. Thank you.
your formula was almost correct, it just needs a $. In B2:
=IFERROR(AVERAGE(OFFSET(Data!B$2,24*(ROW()-2),,24,));"")
eric
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to the thank you (yes, indeed, it can be done!!!), remember to set it to resolved. Thank you.
