0
342
ViewsIs it possible to step through a row of data and sum every n-th cell using a Lambda function?
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?
3 answers
Answer question0
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:
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:
COUNTA(A4:AD4)+COUNTBLANK(A4:AD4)this will return how many columns, including blanks, got your dataOFFSETwill create a range from first to nth column (result from previous step)SUMPRODUCTwill sum up every nth value that row (in my example, every 5 columns)- 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
0
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)
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)
)
)