0

I would like to obtain the cumulated products of an array, also calculated through an array formula.

What that means is, assuming I have an array of numeric values, I want to do a row-wise multiplication of all values up to that row. The following screenshot might clarify the task:

Example_Table_1

I was trying to do it as follows, where the range of 'rates' in the screenshot above is named 'Rates'. I split the vector of n values into an n-times-n matrix where each column includes one more value than the previous one:

Example_Table_2

Here using the formula:

=(ROW(Rates)<TRANSPOSE(ROW(Rates))+1)*Rates

And then I would have to take the product of each column (value > 0 condition) through an array formula, but I didn't find a way to that yet.

Notes:

  1. I'm using Office 365 (x64), but BYCOL() and BYROW() are not available to me.
  2. All values of the 'rates' vector are strictly positive.
  3. I'm trying to avoid VBA to keep the file in .xlsx format since it is often removed from emails otherwise.
  4. I want to use the table as a template where the 'rates' vector would come in various sizes in the future, hence a dynamic array solution would be best.

I'd appreciate your input.

Dattel Klauber
  • 800
  • 1
  • 2
  • 17
  • 2
    I assume you forgot to specify that you want to avoid *volatile* set-ups, since such a solution is straightforward: `=SUBTOTAL(6,OFFSET(INDEX(Rates,1),,,SEQUENCE(ROWS(Rates))))` – Jos Woolley Sep 16 '22 at 14:43
  • The `SCAN` function would provide a simple, non-volatile alternative, though I presume you don't have that function either? `=SCAN(1,Rates,LAMBDA(κ,λ,κ*λ))` – Jos Woolley Sep 16 '22 at 14:51
  • Yes and yes, the 1st solution works perfectly, the 2nd is not available to me. Thanks! I didn't know that you can use arrays that way in `OFFSET()`. If you write it as an answer I can accept it. – Dattel Klauber Sep 16 '22 at 14:56

1 Answers1

1

With SCAN:

=SCAN(1,Rates,LAMBDA(κ,λ,κ*λ))

Without some of the newer O365 functions, a non-volatile solution might not be straightforward. A volatile option would be:

=SUBTOTAL(6,OFFSET(INDEX(Rates,1),,,SEQUENCE(ROWS(Rates))))

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9