Arrayformula to calc sum for dynamic offset range-CodePudding

I already googled a lot and did find some solutions, but I'm afraid, I can't map any of them to my actual problem: I'm trying to calculate values with an arrayformula, based on the last 14 days (the last 14 rows, since every row is 1 day). I want N110:N to have the values (in example: sum) from, let's say, I96:I110. Means, the values in N110 should be sum(I96:I110). For N111 it should be sum(I97:I111) etc.

I already have something like = ARRAYFORMULA("I"& Row(I110:I)-14 & ":I" & Row(I110:I)) which returns

I96:I110
I97:I111
I98:I112
...

in each row.

But I cannot simply wrap this into the arrayformula, since Indirect() is not working here and is returning only the first value. I also tried with offset, which led to the same result.

So, basically I'm trying to use an arrayformula to calc values by a dynamic offset range with a fixed size (14).

I could solve it with google apps script, but I wanted to try with arrayformula.

It would be nice, if someone could help me, thanks in advance!

CodePudding user response：

try:

=INDEX(IFNA(VLOOKUP(ROW(A1:A), 
 QUERY(SPLIT(FLATTEN(ROW(A1:A)&"×"&ARRAY_CONSTRAIN(
 SPLIT(FLATTEN(QUERY(TRANSPOSE(IF(ROW(A1:A)<=TRANSPOSE(ROW(A1:A))=TRUE, 
 TRANSPOSE(A1:A)&"×", )),,9^9)), "×"), 9^9, 14  N("14 day window"))), "×"),
 "select Col1,sum(Col2)
  where Col2 is not null
  group by Col1"), 2, )))

CodePudding user response：

Make sure that N110:N is empty, and then place the following formula in N110:

=ArrayFormula(IF(I110:I="",,SUMIF(ROW(I96:I),"<="&ROW(I110:I),I96:I)-SUMIF(ROW(I96:I),"<="&ROW(I110:I)-15,I96:I)))

This formula assumes that there are no blank cells interspersed between numbers in I96:I (though there may be blank cells after the number list runs out).

Essentially, this sums all cells in in the range up to the current row and then subtracts the total of all cells in rows prior to "15 cells back" as marked from the current cell.