Forum Discussion

danh7's avatar
danh7
Copper Contributor
Dec 05, 2023
Solved

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

danh7's avatar
danh7
Copper Contributor
Dec 06, 2023
Thank you djclements !! This is extremely helpful!
  • 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