Search code examples
google-sheetsgoogle-sheets-formulaarray-formulas

ArrayFormula and PRODUCT of all previous rows?

I have a column of percent changes (column A) and want to create a running total percent change (column B):

   A        B
-------  -------
100.00%  100.00%
 92.75%   92.75%
 97.23%   90.18%
100.00%   90.18%
100.56%   90.68%

This is easy with a formula in each cell of column B, starting with B1 and copying down:

=IF(B1="","",PRODUCT(B$1:B1))

However, I would like to change this to an ArrayFormula to avoid the need to continuously copy that formula down the column when rows are added.

Is this possible using ArrayFormula?

Solution

  • Back to school on this one. So you take logs, do a running sum, then antilog:

    =ArrayFormula(10^mmult(if(column(indirect("A1:"&address(counta(A:A),counta(A:A))))>row(indirect("A1:"&address(counta(A:A),counta(A:A)))),0,1),log10(indirect("A1:A"&counta(A:A)))))

    enter image description here

    I forgot that you don't actually have to create a 2d array to do a running total, so can simplify to

    =ArrayFormula(10^mmult(if(transpose(row((indirect("A1:A"&counta(A:A)))))>
row(indirect("A1:A"&counta(A:A))),0,1),log10(indirect("A1:A"&counta(A:A)))))