I have the following function that automatically sums 3 rows together for a specified number of cells determined by D2 -

=BYCOL((INDEX($1:$11,{4;5;10},SEQUENCE(1,D2,COLUMN(I:I)))),LAMBDA(x,SUM(x)))

The values in each column in rows 4 and 5 I wish to add together and I'd like to subtract the result from the cell value for row 10. I have a workaround which sets the values in row 10 to negative but I'd prefer them to be positive numbers.

I tried the following but unfortunately this doesn't work -

=BYCOL((INDEX($1:$11,{4;5;10},SEQUENCE(1,D2,COLUMN(I:I)))),LAMBDA(x,y,z,SUM((x y)-z)))

Any ideas on how I can adjust the above to make it work please?

Example of expected result:

CodePudding user response：

To make it match with your data:

=BYCOL((INDEX($1:$11,{2;3;4},SEQUENCE(1,Q2,COLUMN(B:B)))),LAMBDA(x,INDEX(x,1) INDEX(x,2)-INDEX(x,3)))

where I've put a suitable value (9) into Q2.

EDIT

This would have been tidier

=BYCOL((INDEX($1:$11,{2;3;4},SEQUENCE(1,Q2,COLUMN(B:B)))),LAMBDA(x,SUMPRODUCT(x,{1;1;-1})))

CodePudding user response：

Different approach - but in my eyes the formula is more readable for what you want to achieve:

The red part is named 'rowHeader', the blue part 'data'

=LET(revenue,FILTER(data,rowHeader="Revenue"),
extraRevenue,FILTER(data,rowHeader="extra Revenue"),
costs,FILTER(data,rowHeader="Costs"),
revenue   extraRevenue-costs)

UPDATE:

You can define the ranges also within the LET-formula

=LET(rowHeader,A6:A15,
data,MAKEARRAY(10,B2,LAMBDA(r,c,INDEX(6:15,r,c 1))),
revenue,FILTER(data,rowHeader="Revenue"),
extraRevenue,FILTER(data,rowHeader="extra Revenue"),
costs,FILTER(data,rowHeader="Costs"),
revenue extraRevenue-costs)

B2 contains the number of months that should be returned.