Arrayformula to calc sum for dynamic offset range

Question

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 have something like = ARRAYFORMULA("I"& Row(I110:I)-14 & ":I" & Row(I110:I)) which returns

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

in each row.

I cannot 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.

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 want to try with arrayformula.

player0 · Accepted Answer · 2021-10-12 17:10:15Z

1

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, )))

answered Oct 12, 2021 at 17:10

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christopher Over a year ago

Thank you, I try that!

Erik Tyler · Accepted Answer · 2021-10-12 20:58:47Z

0

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.

answered Oct 12, 2021 at 20:58

Erik Tyler

9,3952 gold badges9 silver badges11 bronze badges

2 Comments

Christopher Over a year ago

Clear solution, thanks!

Erik Tyler Over a year ago

Glad it worked as you expected. Please take a moment to mark the post as "Best Answer." This alerts the contributor community here as to which issues have been fully resolved, and it helps future site visitors more quickly find relevant answers to issues similar to their own. Thanks.

Collectives™ on Stack Overflow

Arrayformula to calc sum for dynamic offset range

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related