[SOLVED] EXCEL 365 : Creating a dynamic array V of length "N" such as V(n)=V(n-1)*B(n)

EXCEL 365 : Creating a dynamic array V of length "N" such as V(n)=V(n-1)*B(n)

I'm trying to convert a simple recursive calculation into a dynamic array, without using VBA. For demonstration purposes, lets have an array A1=SEQUENCE(5) and try to populate the B column

    B1=10
    B2=$B1*$A1

and extend the function through B6. The result would be:

Of course, the calculation I'm trying to achieve is much more complicated.

If I change the length of the dynamic array in A1, Calculation in B2 will not extend to reflect the new length. It must be dynamic, but trying to reuse an "n-1" result in a dynamic array triggers a circular reference error.

I would expect something like B1=VSTACK(10;B1#*A1#) to be working Any idea on how this can be achieved?

Actual copy paste of the "classic" formula I'm using: Q20=EXP((L19-L20)/$Q$12)*(Q19-$Q$19)+(1-EXP((L19-L20)/$Q$12))*$Q$8*($P$16)+$Q$19 L19# is the spilled array of variable length with by a FILTER function to which the length of Q19 needs to follow.

Thanks

Solution

The SCAN function works like a charm, as long as I only need the immediate previous result

T2 and S2 are Spilled arrays

=VSTACK(
    $T$2;
    SCAN(
        $T$2;
        INDEX(
            OFFSET(S2#;1;0)-S2#;
            SEQUENCE(COUNT(S2#)-1)
        );
        LAMBDA(p;dt;
        EXP(-dt/$M$9)*(p-$T$2)+(1-EXP(-dt/$M$9))*$M$5*($M$2)+$T$2)
    )
)

Although I moved stuff around, the LAMBDA function is pretty muche the same. Now I only need to change the spilled arrays for their named array counterpart with a combination of OFFSET and SEQUENCE functions