Is it possible to write a recursive formula in excel to add products of array elements moving in opp-CodePudding

I have a table of X Y and Z below:

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.

CodePudding user response：

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.

CodePudding user response：

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.