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.

Answer 2

The only way I can find is to use DPRODUCT with OFFSET, but that requires a title row. It does not matter what is in the title row(it can even be empty), just that it is included.

=DPRODUCT(OFFSET(A1,0,0,SEQUENCE(COUNT(A:A),,2)),1,$ZZ1:$ZZ2)

The $ZZ1:$ZZ2 can be any empty cell reference.

If the values in A are dynamic then we can do:

=DPRODUCT(OFFSET(A1,0,0,SEQUENCE(ROWS(A2#),,2)),1,$ZZ:$ZZ)

Answer 3

There are plenty of interesting answers here. But, if summation is easy why not take logarithms of the number you want to multiply, sum those logarithms and then calculate the exponent of your sum to return to the product of the original numbers.

i.e. exploit the fact that ln(a * b) = ln(a) + ln(b)