Excel Dynamic Array formula to create a running product of a column

Question

I need to create a running product from a column of numbers (I could use a row, but a column is easier to demonstrate here.) The input might be any arbitrary array. In fact, in the application where I would deploy this, it will not be a range, but rather another dynamic array within a LAMBDA formula. Here is an example of the Input column of numbers and the desired Output from the formula:

Inputs	Expected Dynamic Array Output
10	10
8	80
3	240
4	960
5	4800

The formula would spill the results.

There are lots of solutions for a running total, but I've found no solution for a running product. I have tried a few different approaches, including SUBTOTAL and AGGREGATE with no success. I have also built a number of approaches that get the result, but are hard-coded to a fixed number of rows. I need the formula to adapt to any arbitrarily sized number of rows. The following formula is the closest I have gotten so far.

This LET formula delivers the result, but, as you can see is fixed to 5 rows:

=LET( a, {10;8;3;4;5},
       v, SEQUENCE( ROWS(a) ), h, TRANSPOSE( v ),
       stagr, (v - h + 1) * (v >= h),
       m, IFERROR(INDEX( a, IF(stagr>0,stagr,-1), ), 1),
       almost, INDEX(m,v,h) * INDEX(m,v,h+1) * INDEX(m,v,h+2) * INDEX(m,v,h+3) * INDEX(m,v,h+4),
       result, INDEX( almost, , 1 ),
       result )

The arbitrary array of numbers input is placed in the variable a.

The next step is to create some indexes that will be used to address these numbers: v is a sequence of vertical rows for each number in a and h is a the same sequence, but transposed into columns. stagr is an index matrix that is created from v and h that will later be used to address each item in a to form it into a multiplication matrix. If you replace the last result with stagr, you can see the shape of stagr. It just shifts a column down by one row until they are shifted all the way down.

Now we create the mulitplication matrix m using stagr by simply using INDEX, like this: INDEX(a,stagr). But this is not exactly what is needed because it takes the first row value (10) and replicates it because an INDEX of 0 is treated the same as 1. To get what we want, I forced an error by using and internal IF statement like this: INDEX( a, IF(stagr>0,stagr,-1) ) to replace the 0 results with -1. i.e. it will produce this:

Now, replace the errors with 1's by using IFERROR, so this explains how m is created and why. The result is a matrix like this:

and by multiplying m row-wise, we get the output we want, but this is where I fail.

For illustration, I created a variable almost that shows how I am trying to do a row-wise multiplication.

almost, INDEX(m,v,h) * INDEX(m,v,h+1) * INDEX(m,v,h+2) * INDEX(m,v,h+3) * INDEX(m,v,h+4)

You can see that I crudely multiplied one column times the next and the next... and using h + offset to get there. This produces the almost matrix and result just delivers the first column of that matrix, which contains the answer.

While an answer might be a good replacement for almost that would be dynamically sized, that is not my real question. I want a running product and I suspect that there is a wholly different approach than simply replacing my almost.

Just to be clear, the result must be a dynamic array that spills with no helper cells or CSE drag-down.

oh... and no VBA. (@stackoverflow - please add a no-VBA tag)

Answer 1

Whilst not available to everybody (yet) we can use SCAN()

Formula in A1:

=SCAN(1,{10,8,3,4,5},LAMBDA(a,b,a*b))

• The 1st parameter is our starting value, meaning the 1st calculation in the nested LAMBDA() is '1*10'.
• The 2nd parameter can both take a 1D- & 2D-array (written or range-reference).
• The 3rd parameter is a nested LAMBDA() where the result of our recursive function will then be used for the 2nd calculation; '10*8'. And the 3rd...etc. etc.
• In the above sample a vertical array is spilled but when horizontal input is used this will obviously result in an horizontal spilled output. When a 2D-array is used this will spill a 2D-array as result.