2

I need to calculate a diagonal total of numbers in a dynamic array on an Excel sheet. The number of columns will increase but there will always be seven rows in the tableau, so I need the formula to automatically adjust to the number of columns in the array.

Example of array and results underneath

In the example results the first column has only one figure to total, (215). The second needs to add (249) and (246) giving a result of (455) and so on.

Obviously I can just create a series of formulae to add the relevant cells in the results, but that doesn't meet the need for the formula results to adjust to the size of the input array.

I've been scratching my head over whether there is a clever matrix solution to this, but my maths isn't good enough.
Subcontract Current month (215) (249) (39) (139) (267) (130) (356) (126) (193) (403)
Next month (206) (205) (40) (190) (225) (205) (274) (120) (261) (381)
Month + 2 (176) (189) (50) (135) (279) (156) (219) (129) (241) (304)
Month + 3 - (8) - (23) - - (19) - - (26)
Month + 4 - - - - - - - - - -
Month + 5 - - - - - - - - - -
Month + 6 - - - - - - - - - -
(597) (650) (130) (487) (771) (491) (868) (374) (696) (1,115)
Subcontract out of stock 215 455 420 367 516 491 863 555 532 813

In the original spreadsheet the Subcontract data is in the form of an x by 7 dynamic array, in this case 10 columns by 7 rows.

"Subcontract out of Stock" is what I'm aiming for. In each column it is the total of current month plus "Next month" from the previous column (if it exists) plus "Month + 2" from two columns before (if it exists) and so on. Each output has the sign changed.

OK, I did this and it gives me what I needed:

  =MAKEARRAY(1,COLUMNS(C130#),
   LAMBDA(r,c,
      OFFSET(C130,r-1,c-1)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),1,-1)),0)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),2,-2)),0)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),3,-3)),0)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),4,-4)),0)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),5,-5)),0)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),6,-6)),0)
   )
)
VaughanD
  • 21
  • 2
  • Make a template with 1 and 0, where the 1's are placed for the numbers you want to sum. Then sumproduct(). I would attempt but without copyable data I don't follow your example. – Solar Mike Jun 27 '23 at 13:23
  • Please provide sample data in [Table Markdown](https://www.tablesgenerator.com/markdown_tables) format. Just copy your data from Excel, generate it and copy it back to your question leaving a blank line. Provide also the expected output. It helps members from this community trying to help you to reproduce the sample and you because your question gains more attention and possible more answers. Thanks – David Leal Jun 27 '23 at 13:40

0 Answers0