I have an array of 2 columns in which each cell should do an add-operation belonging to it's predecessor. The length of the array depends on a 3rd column which length varies. So I decvided to use an ArrayFormula to do the operations:
=ARRAYFORMULA($D2:D+$C3:C)
Starting in Cell D3 this should take the value of it's predecessor row and add the value of it's left neighbor column. Unfortunately this doesn't work because ArrayFormula doesn't support arrays with different lengthes.
My first step to solve this was to make the arrays the same length by defining them manually:
=ARRAYFORMULA($D2:D290+$C3:C291)
This works but I found out it's performance is way too slow. I can litarally watch it filling the column cell by cell. This depends on the needed pre-calculations which always rais CellChanged-Events starting the next calculation. It seems, this cannot be parallelized.
Question 1: Is there a way to perform this operation faster? As long as I keep the arrays with a fixed length I could use simple add-operations in each cell but that wouldn't help with my second problem.
Question 2: Is there a way to do these calculations on arrays with varying lengthes?
Example how it looks (fixed array-length) (Cropped to show, how the values really are added
I added a new sheet ("Erik Help"). In that sheet, I deleted everything from Column D, including the header, and replaced it with the following formula in D1:
=ArrayFormula({"Div/SLR-"&CHAR(10)&"Kumuliert";IF(A2:A="";;SUMIF(IF(ROW(A2:A);ROW(A2:A));"<="&ROW(A2:A);C2:C))})
The &CHAR(10)& in the middle of the header isn't strictly necessary; I just felt it made for a neater sheet, since it allows for controlled breaking of the header and decreasing the width of the column.
The rest of the formula basically reads, in plain English, "Continue to sum rows from Column C that are less than or equal to each row in Column D moving downward, one by one."
As for the slow processing speed in your sheet, you have over 31,000 rows in that sheet, which means your formulas must process them all, even though most of them are unnecessary. You'll notice that my added sheet is trimmed in both rows and columns to contain only slightly more than you'll probably need; and as such, my formulas process as soon as your A1 formula loads.
Also, it is unclear to me why your A1 formula asks to start receiving data from March 3, 2020 when your first "Div/SLR-List" date isn't until June. I would recommend replacing that "from" date in your A1 formula with a reference to K2.
