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?
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)