Say I have numbers in A1 to A20 and I wanted to sum non-overlapping ranges of 5 cells in column A and store the results in cells in column E, it would look something like this (if the formulas were typed into each E column cell manually):
E1 = SUM(A1:A5)
E2 = SUM(A6:A10)
E3 = SUM(A11:A15)
E4 = SUM(A16:A20)
However, I don't want to type the formulas into E cells manually. I want to be able to select the formula in E1 and drag it down to E4, whilst maintain the non-overlapping ranges of 5 cells in A! Excel does not give me this behavior by default, it does this instead:
E1 = SUM(A1:A5)
E2 = SUM(A2:A6)
E2 = SUM(A3:A7)
E4 = SUM(A4:A8)
See how the ranges of 5 cells in each SUM() function overlap? e.g. A1:A5 and A2:A6. That's not what I want.
So, what is a formula that would enable me to do this? Basically, the following pseudocode would work, but I can't seem to implement anything like it in Excel:
SUM(CELL(COLUMN, (CURRENT_ROW - 1) * 5 + 1):CELL(COLUMN, (CURRENT_ROW - 1) * 5 + 5))
For example, for E2, CURRENT_ROW = 2, meaning it would look like this:
SUM(CELL(A, (2 - 1) * 5 + 1):CELL(A, (2 - 1) * 5 + 5))
= SUM(CELL(A, 6):CELL(A, 10))
This pseudocode assumes CELL has the method signature CELL(row, column).