SOLVED

Is there a way to complete the below without manually doing it?

Copper Contributor

Is there a way to complete the below without manually doing it?

Hi,

I'm looking to find an easier, faster and more efficient way to complete the example below. Is there a formula that can help me with this? So I have three rows, as follows, with the formula I'm hoping not to write manually in the third row:

 Row 1 A B C D E F G H I Row 2 1 2 3 4 5 6 7 8 9 Formula A*1 B*1+A*2 C*1+B*2+A*3 D*1+C*2+B*3+A*4 E*1+D*2+C*3+B4+A*5 F*1+E*2+D*3+C*4+B*5+A*6 ... ... ...

First time posting here (just created an account) so I apologize if I'm not following any guidelines or if this isn't clear enough. Thanks for the help with this!

3 Replies
best response confirmed by danh7 (Copper Contributor)
Solution

Re: Is there a way to complete the below without manually doing it?

@danh7 If you have Excel for MS365, try the following formula in cell A3, then drag it across:

``=SUMPRODUCT(\$A\$1:A1, SORTBY(\$A\$2:A2, COLUMN(\$A\$2:A2), -1))``

The SORTBY function is used to reverse the order of the second row. The SUMPRODUCT function sums the results of multiplying row 1 with row 2 in reverse order.

If you want to accomplish the same thing using a single-cell dynamic array formula, the MAKEARRAY function can be used with CHOOSECOLS and SEQUENCE:

``````=LET(
arr, A1:I2,
MAKEARRAY(1, COLUMNS(arr), LAMBDA(r,c,
SUMPRODUCT(
CHOOSECOLS(TAKE(arr,1), SEQUENCE(c)),
CHOOSECOLS(DROP(arr,1), SEQUENCE(c,,c,-1))))))``````

Please see the attached workbook, if needed...

Re: Is there a way to complete the below without manually doing it?

Thank you @djclements !! This is extremely helpful!

Re: Is there a way to complete the below without manually doing it?

@danh7  so I found this one interesting and wanted to see what alternative solutions there are.  So i found the solution below but in no way saying it is better than the previous, just a different approach.  So, I created an alternative to the reverse order (there are many options) that is independent of row/column orientation.  Then I created a Calculation using SCAN and did a sum only to make sure the parts are correct but would replace it with REDUCE and have the LAMBDA(p,q, p+ ... ) to perform the sum in a 'final' version.

``````=LET(inA, A16:A18, inB, B16:B18,
r, ROWS(inB),c,COLUMNS(inB),
revB, INDEX(inB, SEQUENCE(r,c,r*c,-1)),
out,SCAN(0,SEQUENCE(r,c),LAMBDA(p,q, SUMPRODUCT(TAKE(inA,q,q),TAKE(revB,-q,-q)))),
SUM(out))``````

so line 1 defines inputs

line 2 finds #rows and #columns

line 3 reverses input B

line 4 does the calculations

line 5 outputs the sum (remover the SUM to see the output parts

1 best response

Accepted Solutions
best response confirmed by danh7 (Copper Contributor)
Solution

Re: Is there a way to complete the below without manually doing it?

@danh7 If you have Excel for MS365, try the following formula in cell A3, then drag it across:

``=SUMPRODUCT(\$A\$1:A1, SORTBY(\$A\$2:A2, COLUMN(\$A\$2:A2), -1))``

The SORTBY function is used to reverse the order of the second row. The SUMPRODUCT function sums the results of multiplying row 1 with row 2 in reverse order.

If you want to accomplish the same thing using a single-cell dynamic array formula, the MAKEARRAY function can be used with CHOOSECOLS and SEQUENCE:

``````=LET(
arr, A1:I2,
MAKEARRAY(1, COLUMNS(arr), LAMBDA(r,c,
SUMPRODUCT(
CHOOSECOLS(TAKE(arr,1), SEQUENCE(c)),
CHOOSECOLS(DROP(arr,1), SEQUENCE(c,,c,-1))))))``````

Please see the attached workbook, if needed...