Search code examples
excelexcel-formuladynamic-arrays

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

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:

  1. Cumulated Products (Multiplications) of Array in Excel
  2. Excel Dynamic Array formula to create a running product of a column

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.

Example Screenshot

Solution

  • 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:

    sample excel file

    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).