CODEWITHSUNDEEP
excelexcel-formula
craigsnyders
craigsnyders

Reputation: 107

Drag-down increment by 8?

I need to create an average of every 8 rows. Without writing it manually, I want cells G3:G18 to have formulae like:

=MIN(C1:C8)
=MIN(C9:C16)
=MIN(C17:C24)
...

The data from B1:C25 looks as follows:

B   C
1   4597
1   4006
1   3676
1   3690
1   3586
1   4978
1   3651
1   4316
2   2055
2   2091
2   3133
2   3771
2   3523
2   2567
2   2135
2   7973
3   1497
3   5300
3   3425
3   2342
3   1637
3   1512
3   1577
3   1484
4   1710
4   1286
...

Whenever I drag my formula down, it only increments the range in the MIN formula by 1. I need it to increment by 8. I've looked into INDIRECT, and ADDRESS, but really can't work out the syntax I need!

Upvotes: 0

Views: 1429

Answers (1)

barry houdini
barry houdini

Reputation: 46361

Try this formula in G3 copied down

=MIN(OFFSET(C$1,(ROWS(G$3:G3)-1)*8,0,8))

Upvotes: 3

Related Questions