Excel Dynamic Solution for Cumulated Product / Running Product of an Array without SCAN or OFFSET

Question

I want to calculate the cumulated / running product of an array.

However, the solutions provided under these two questions are not quite what I want:

In both of the solutions provided under those questions, the solution uses either the SCAN() function which is currently not available to me, or it uses OFFSET() which only allows for a range-type input as array, not an array generated with a function such as SEQUENCE().

More explicitly: The array I want to calculate a running product of is =SEQUENCE(D11,1,1-D23,D24) where D11 is an integer, D23 and D24 are decimals.

Does anyone have an in-formula solution for this or do I have to do the intermediary step to 'write' the sequence into a range first and then use the OFFSET() function?

Edit 1:

An example for the SEQUENCE() function above would be {0.97, 0.96, 0.95, ..., 0.75}

Edit 2:

I would of course be happy with an OFFSET() solution if there is a way to use it without a range-type array as input.

Edit 3:

Here is a numerical example of what I am trying to achieve without the itermediary steps.

@DattelKlauber what excel version do you have so we know what functions to use? You are trying to use a cumulative but your input is not a range and, so you need to use functions that support arrays as input. Would you provide a sample data (input and output) so we can understand better the scenario? Thanks — David Leal, Oct 24 '22 at 16:44
I think you can take logs then progressively add them using mmult but I don't have a computer available to check it — Tom Sharpe, Oct 24 '22 at 17:45
@PeterT OP wants a running product, ie with the array: `{0.97, 0.96, 0.95, ..., 0.75}` It would return `{0.97,0.9312,0.88464,...,0.02908}` not a single sum. — Scott Craner, Oct 24 '22 at 18:25
Yes, I'm trying to achieve what @ScottCraner pointed out. DavidLeal please see Edit 3 for a numerical example. Thanks for taking a look at this — Dattel Klauber, Oct 24 '22 at 18:43

Scott Craner · Answer 1 · 2022-10-25T17:31:16.633

5

Modifying the OP's question starting formula from the second link and using LN and EXP. We can use MMULT to get where we want:

=LET( a, LN(A1#),
       v, SEQUENCE( ROWS(a) ), 
       h, TRANSPOSE( v ),
       stagr, (v - h + 1) * (v >= h),
       m, IFERROR(INDEX( a, IF(stagr>0,stagr,-1), ), 0),
        EXP(MMULT(m,SEQUENCE(ROWS(m),,1,0))))

You should be able to replace the A1# with your SEQUENCE formula:

=LET( a, LN(SEQUENCE(D11,1,1-D23,D24)),
       v, SEQUENCE( ROWS(a) ), 
       h, TRANSPOSE( v ),
       stagr, (v - h + 1) * (v >= h),
       m, IFERROR(INDEX( a, IF(stagr>0,stagr,-1), ), 0),
        EXP(MMULT(m,SEQUENCE(ROWS(m),,1,0))))

edited Oct 25 '22 at 17:31

answered Oct 24 '22 at 19:10

Scott Craner

148,073
10
49
81

Log solution is really clever, didn't think of that at all. Those functions are definitely available to me but I'll have to test it tomorrow. Thanks! – Dattel Klauber Oct 24 '22 at 20:34
The formula works, thanks! The one presented by DavidLeal is a bit easier to understand though so I'll accept that one. Thanks again for your effort – Dattel Klauber Oct 25 '22 at 08:37

David Leal · Accepted Answer · 2023-02-21T22:49:24.190

Based on your initial formulation and requirements and assuming the input data set has only positives values. On F2 you can put the following formula. Since you can use SEQUENCE I assume you can use LET too.

=LET(n, B2, seq,LOG10(SEQUENCE(n,1,1-B3,B4)), seqInc, INT(SEQUENCE(n,n,0)/n)+1, 
 LUnitTriMatrix, --(seqInc >= TRANSPOSE(seqInc)), mult,
 MMULT(LUnitTriMatrix, seq), POWER(10, mult))

as @Dattel Klauber pointed in the comments to generate the LUnitTriMatrix can be simplified as follow: =N((SEQUENCE(n)>=TRANSPOSE(SEQUENCE(n)))) so the formula can be simplified as follow.

=LET(n, B2, seq,LOG10(SEQUENCE(n,1,1-B3,B4)), 
 seqInc, SEQUENCE(n), LUnitTriMatrix, N(seqInc >= TRANSPOSE(seqInc)), 
 mult,MMULT(LUnitTriMatrix, seq), POWER(10, mult))

Note: For this case N() function and --() operation both can be used:

This solution doesn't require to create a range with the sequence, instead it just uses the array generated based on sequence input parameters.

Here is the output:

Note: Columns Sequence and Manual are just for testing purpose.

Explanation

The solution uses the following two main ideas:

IDEA 1: The solution suggested in the answer provided by @Alister of the question you shared as a reference: Excel Dynamic Array formula to create a running product of a column. The idea is to use the following logarithm property:

log(a*b) = log(a) + log(b)

and because log(a) is the inverse operation of exp(a), i.e.

a = exp(log(a)), where a > 0

therefore

a*b = exp(log(a*b)) = exp(log(a) + log(b))

so we convert a product cumulated problem into a sum cumulated problem of log items.

IDEA2: To build Lower Unitary Triangular Matrix (LUnitTriMatrix for future reference) so we can use a matrix multiplication via MMULT. For example:

|1 0 0|   |a|    |a         |
|1 1 0| x |b|  = |a + b     |
|1 1 1|   |c|    |a + b + c |

The LUnitTriMatrix can be obtained via the following two sequences:

 seqInc, INT(SEQUENCE(n,n,0)/n)+1

for n equal 5 for example the output is:

|1 1 1 1 1|
|2 2 2 2 2|
|3 3 3 3 3|
|4 4 4 4 4| 
|5 5 5 5 5|

Note: The same can be achieved as follow: TRANSPOSE(MOD(SEQUENCE(n,n,0),n)+1), it is a matter of preference.

and the corresponding transpose matrix: TRANSPOSE(seqInc):

The following condition generates the desired matrix:

LUnitTriMatrix, --(seqInc >= TRANSPOSE(seqInc))

For example:

|1 1 1 1 1 1|    |1 2 3 4 5|   |1 0 0 0 0|
|2 2 2 2 2 2|    |1 2 3 4 5|   |1 1 0 0 0| 
|3 3 3 3 3 3| >= |1 2 3 4 5| = |1 1 1 0 0|
|4 4 4 4 4 4|    |1 2 3 4 5|   |1 1 1 1 0|
|5 5 5 5 5 5|    |1 2 3 4 5|   |1 1 1 1 1|

The rest is just to do the multiplication:

mult, MMULT(LUnitTriMatrix, seq)

and the inverse operation (power of ten): POWER(10, mult) gets the final result.

Note: I use LOG10(x)/POWER(10,x) because it is easier for testing purpose, but any other combination of log/exp function can be used with the same base, such as: LN(x)/EXP(x) or LOG(x,b)/POWER(b,x).

Wow, yes all of the functions in your formula are available to me. I'll have to test it out tomorrow, thanks! — Dattel Klauber, Oct 24 '22 at 20:33
Great explanation. This could also hold an array like in the second linked question. `=LET(a,{10;8;3;4;5},n, COUNTA(a), seq,LOG10(a), seqInc, INT(SEQUENCE(n,n,0)/n)+1, LUnitTriMatrix, --(seqInc >= TRANSPOSE(seqInc)), mult, MMULT(LUnitTriMatrix, seq), POWER(10, mult))` — P.b, Oct 25 '22 at 05:27
@P.b, is the formula you posted a generalization of the one in the answer? i.e. will it work regardless of the input being a row vector or col vector? — Dattel Klauber, Oct 25 '22 at 07:51
@DavidLeal, formula works, thanks! and great explanation. For `LUnitTriMatrix ` the `INT` function seems to be a complicated though. The same matrix can simply be generated with `N(SEQUENCE(n)>=TRANSPOSE(SEQUENCE(n)))` — Dattel Klauber, Oct 25 '22 at 08:02
@DattelKlauber I was not aware of this option to generate an `nxn` matrix with a single row/column sequence. I will include in my answer. Thanks — David Leal, Oct 25 '22 at 12:40
@DattelKlauber my understanding from P.b comment is that you can use as input any array you create in the form: `{a;b;c..;z}` instead of generating the sequence. The array has to be of size: `nx1` (column array) otherwise the `MMULT` would not work. I don't see any other difference from my formulation. Thanks P.b for your comment! — David Leal, Oct 25 '22 at 13:02
@Dattel Klauber An iferror could be used to repeat the formula with transposed array, but I don't see the point of that using a manual array. The point was that the formula would work without any range involvement. (So yeah David, you understood it correctly) — P.b, Oct 25 '22 at 16:04

Excel Dynamic Solution for Cumulated Product / Running Product of an Array without SCAN or OFFSET

2 Answers2

Explanation

Linked