Forum Discussion

danh7's avatar
danh7
Copper Contributor
Dec 05, 2023

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 1ABCDEFGHI
Row 212 3456789
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!

excel
Formulas and Functions
Macros and VBA
  • djclements's avatar
    djclements
    Dec 06, 2023

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

  • djclements's avatar
    djclements
    Bronze Contributor
    Dec 06, 2023

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

      • mtarler's avatar
        mtarler
        Silver Contributor
        Dec 07, 2023

        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

Resources