SOLVED

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

Copper Contributor

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 1ABCDEFGHI
Row 213456789
FormulaA*1B*1+A*2C*1+B*2+A*3D*1+C*2+B*3+A*4E*1+D*2+C*3+B4+A*5F*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

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

Thank you @djclements !! This is extremely helpful!

@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

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

View solution in original post