Sum the current row plus the next 6 (7 total)

Question

I have a large spreadsheet and I want to sum the current row + the next 6 rows in a computed column (in this case, column O). So if I'm on row 21, for example, I want to sum column G from G21 to G27. Then on row 22, I want to sum column G from G22 to G28, etc. etc. I can do this easily with a formula in each cell, but I want to set a single ARRAY statement at the top and let it do all the work in each subsequent row. I've got this working beautifully with rankings and other kinds of calculations, but I'm stymied as to this one. Can you make any recommendations?

Separately, I'd also like to extrapolate in similar fashion the 7 day average in a separate column (column P). I'm hoping that the same ARRAY formula will be useable for this as well.

Not sure if it's relevant or not, but I'm using the standard model of arrayformula coupled with an isblank statement to stop processing at the last row. This is data imported using importjson, so the number of rows varies.

Answer 1

Suppose that your Col-G numbers run G2:G.

Place the following formula in O2:

=ArrayFormula(IF(G2:G="",,SUMIF(ROW(G2:G),"<"&ROW(G2:G)+7,G2:G)-SUMIF(ROW(G2:G),"<"&ROW(G2:G),G2:G)))

Then place the following formula in P2:

=ArrayFormula(IF(G2:G="",,O2:O / COUNTIFS(G2:G,"<>",ROW(G2:G),"<"&ROW(G2:G)+7,ROW(G2:G),">="&ROW(G2:G))))

As for the Col-O formula, we cannot use SUMIFS as one might expect with ArrayFormula( ). That is because SUMIFS itself relies on complex arrays in its functionality, and we'd essentially be asking Sheets to do a 3D array, which it cannot, strictly speaking.

Instead, we simply get a SUMIF of of all numbers in rows less than the current row number +7 and then subtract a second SUMIF of any rows with row numbers less than the current row. This results in the target range of seven rows added per row.

We need the somewhat complex Col-P formula to act on the Col-O formula because your final rows will not be divided by a uniform 7, but rather by however many rows had numbers in Col-G. For instance, the penultimate data row must be divided by 2, not 7; and the final row by 1, not 7.

The functionality of the COUNTIFS is similar to what was done with the two SUMIF formulas. However, COUNTIFS does work with ArrayFormula; so it can be constructed with all elements of the separate SUMIF formulas as its parameters.