title: Excel Dynamic Array formula to create a running product of a column
body: <p>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:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: right;">Inputs</th>
  <th style="text-align: right;">Expected Dynamic Array Output</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: right;">10</td>
  <td style="text-align: right;">10</td>
  </tr>
  <tr>
  <td style="text-align: right;">8</td>
  <td style="text-align: right;">80</td>
  </tr>
  <tr>
  <td style="text-align: right;">3</td>
  <td style="text-align: right;">240</td>
  </tr>
  <tr>
  <td style="text-align: right;">4</td>
  <td style="text-align: right;">960</td>
  </tr>
  <tr>
  <td style="text-align: right;">5</td>
  <td style="text-align: right;">4800</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The formula would spill the results.</p>
  <p>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.</p>
  <p>This LET formula delivers the result, but, as you can see is fixed to 5 rows:</p>
  <pre><code>=LET( a, {10;8;3;4;5},
         v, SEQUENCE( ROWS(a) ), h, TRANSPOSE( v ),
         stagr, (v - h + 1) * (v &gt;= h),
         m, IFERROR(INDEX( a, IF(stagr&gt;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 )
  </code></pre>
  <p>The arbitrary array of numbers input is placed in the variable <strong>a</strong>.</p>
  <p>The next step is to create some indexes that will be used to address these numbers: <strong>v</strong> is a sequence of vertical rows for each number in <strong>a</strong> and <strong>h</strong> is a the same sequence, but transposed into columns. <strong>stagr</strong> is an index matrix that is created from <strong>v</strong> and <strong>h</strong> that will later be used to address each item in <strong>a</strong> to form it into a <em>multiplication matrix</em>.  If you replace the last <strong>result</strong> with <strong>stagr</strong>, you can see the shape of <strong>stagr</strong>. It just shifts a column down by one row until they are shifted all the way down.</p>
  <p><a href="https://i.stack.imgur.com/lke0i.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lke0i.png" alt="stagr" /></a></p>
  <p>Now we create the mulitplication matrix <strong>m</strong> using <strong>stagr</strong> by simply using INDEX, like this: <code>INDEX(a,stagr)</code>. 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: <code>INDEX( a, IF(stagr&gt;0,stagr,-1) )</code> to replace the 0 results with -1. i.e. it will produce this:</p>
  <p><a href="https://i.stack.imgur.com/EXzGW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EXzGW.png" alt="m with errors" /></a></p>
  <p>Now, replace the errors with 1's by using IFERROR, so this explains how <strong>m</strong> is created and why.  The result is a matrix like this:</p>
  <p><a href="https://i.stack.imgur.com/wqkSs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wqkSs.png" alt="multiplication matrix" /></a></p>
  <p>and by multiplying <strong>m</strong> row-wise, we get the output we want, but <strong>this is where I fail</strong>.</p>
  <p>For illustration, I created a variable <strong>almost</strong> that shows how I am trying to do a row-wise multiplication.</p>
  <p><code>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)</code></p>
  <p>You can see that I crudely multiplied one column times the next and the next... and using <strong>h + <em>offset</em></strong> to get there.  This produces the <strong>almost</strong> matrix and <strong>result</strong> just delivers the first column of that matrix, which contains the answer.</p>
  <p>While an answer might be a good replacement for <strong>almost</strong> 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 <strong>almost</strong>.</p>
  <p>Just to be clear, the result must be a dynamic array that spills with no helper cells or CSE drag-down.</p>
  <blockquote>
  <p><em>oh... and no VBA. (@stackoverflow - please add a no-VBA tag)</em></p>
  </blockquote>