2

I have a model that produces an array of values that can be several hundred columns wide. Every 25th column contains a number that I need to add to the total.

I thought the cleanest solution would be creating a LAMBDA function that would take the starting cell from the user's input and then offset across the row 25 cells, add the value there to a running total, and continue the offset stepping until it reached an empty cell.

Is it possible? How to do it?

ZygD
  • 22,092
  • 39
  • 79
  • 102
Blake
  • 23
  • 4

3 Answers3

2

I don't think it's possible to loop as you wish in Excel with normal functions (you can do it with VBA tough). But you may benefit from DESREF and SUMPRODUCT to sum up values every n-th columns.

As example I made a fake dataset:

enter image description here

Got values from columns 1 to 30 (A to AD). I want to sum up values every 5 columns (1, 5, 10, 15,... and so on). The borded cells are the manual calculated results to understand logic but you can do it in a single formula:

=SUMPRODUCT(--(COLUMN(OFFSET(A4;0;0;1;COUNTA(A4:AD4)+COUNTBLANK(A4:AD4)))/5=INT(COLUMN(OFFSET(A4;0;0;1;COUNTA(A4:AD4)+COUNTBLANK(A4:AD4)))/5))*A4:AD4)+A4

This is how it works:

  1. COUNTA(A4:AD4)+COUNTBLANK(A4:AD4) this will return how many columns, including blanks, got your data
  2. OFFSET will create a range from first to nth column (result from previous step)
  3. SUMPRODUCT will sum up every nth value that row (in my example, every 5 columns)
  4. We manually add the first input, column 1, at the end of the formula

If you want every 25 columns, just replace the /5 with /25

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • Sorry for taking so long to get back to this. This did not work exactly, but it gave me ideas on how to proceed and after several attempt I got it working. However, I just noticed Paul's suggestion below involving the recursive Lambda function and that is what I originally envisioned. I will keep working on this and post my end result. – Blake May 02 '22 at 00:49
  • I ended up with this formula: "=SUMPRODUCT(Z30:EA30,--(MOD(COLUMN(Z30:EA30)-COLUMN(Z30),25)=0))". The ranges are set by VBA code that builds out the columns, thus establishing the array. Each time the model is run and a different number of columns are generated, the formula is reset and the calculation is done correctly. Thanks again Firefox. – Blake May 02 '22 at 01:16
1

You should be able to do this with a recursive LAMBDA.

StridingSum = LAMBDA(cell, step, [start],
    LET(
        colOffset, IF(ISOMITTED(start), -1, start) + step,
        value, OFFSET(cell, 0, colOffset),
        IF(
            value = "", 
            0,
            value + StridingSum(cell, step, colOffset)
        )
    )
);

Which you call as =StridingSum(A2, 25) (assuming your data started in A1)

Paul.s
  • 38,494
  • 5
  • 70
  • 88
  • Thanks Paul -- this is what I was looking for. I have a functioning equation now thanks to some input from Firefox above, but this feels like it will work in a more elegant fashion. I am going to try this and will post back the results. – Blake May 02 '22 at 00:50
  • After a brief attempt to replace what I have with this, I have changed my mind. I am currently utilizing VBA code to set the formula to the correct number of columns in the array and change everything now would be a bit of work. I will keep this example in my notes though and likely use it in the future. Thanks again Paul. – Blake May 02 '22 at 01:15
0

This might work slightly better than Paul's in allowing you to select the first cell of data rather than the second.

StridingSum = LAMBDA(cell, step, [start],
LET(
    colOffset, IF(ISOMITTED(start), 0, start + step),
    value, OFFSET(cell, 0, colOffset),
    IF(
        value = "", 
        0,
        value + StridingSum(cell, step, colOffset)
    )
)
General Grievance
  • 4,555
  • 31
  • 31
  • 45
Ally Mitchell
  • 213
  • 3
  • 7