CODEWITHSUNDEEP
excelrecursion
Sam Schoenberg
Sam Schoenberg

Reputation: 21

Is it possible to write a recursive formula in excel to add products of array elements moving in opposite directions?

I have a table of X Y and Z below:

Table of x y and z

As the formula in Z3 spells out, column Z contains the sum of the products of the last X and first Y, first X and last Y, and everything in between. I'm not sure how to put it into technical terms, but I guess I'm looking for the sum of products of two arrays moving in opposite directions? I am wondering what the recursive formula for this would be, and then if that recursive formula has any way of being implemented in excel.

Upvotes: 0

Views: 3877

Answers (4)

Bam
Bam

Reputation: 585

Solving old problems with new Excel Solutions. This uses the map function to iterate through x and y, then creates a custom lambda function to shorten some of the calculations:

=LET(
    x, X1:X3,
    y, Y1:Y3,
    MAP(
        SEQUENCE(ROWS(x)),
        LAMBDA(v,
            LET(
                r, LAMBDA(a, ROWS(INDEX(a, 1):INDEX(a, v))),
                SUMPRODUCT(
                    INDEX(x, SEQUENCE(r(x))),
                    INDEX(y, SEQUENCE(r(y), , r(y), -1))
                )
            )
        )
    )
)

enter image description here

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34355

Well to do recursion you would need access to the Lambda function - not sure if this generally available, it was only on the Beta channel of Excel 365 last time I looked. So you would create a formula like this:

=LAMBDA(x,y,ix,iy,IF(iy=0,0,XY(x,y,ix+1,iy-1)+INDEX(x,ix)*INDEX(y,iy)))

and add it to the Name Manager like this:

enter image description here

enter image description here

... although you would probably say that it is merely iteration dressed up as recursion ;-)

if you need to get the sum for the first one, two, three rows, use

=XY(X:X,Y:Y,1,ROW())

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

If you have Excel 2021 or Excel for Microsoft 365, you can use:

Z1: =SUMPRODUCT(INDEX($X:$X,SEQUENCE(COUNT($X$1:X1))),INDEX($Y:$Y,SEQUENCE(COUNT($Y$1:Y1),,COUNT($Y$1:Y1),-1)))

and fill down as far as needed.

enter image description here

Upvotes: 0

BuJay
BuJay

Reputation: 127

Note specifically addressing your question, but this may help. Have you considered reversing one array and then doing a regular sumproduct or sum((xxx)*(yyy)), i.e., array product?

For example, you can reverse the array in column Y with the following:

In cell Z1, write this:

=INDEX($Y$1:$Y$3,ROWS($Y$1:$Y$3)-ROW()+1)

and then copy and paste down to row 3.

Upvotes: 0

Related Questions