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.