1

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 it doesn't work:

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

Example of expected result:

Jan Feb Mar Apr May Jun Jul Aug Sept
Revenue 10000 12000 14000 16000 11000 20000 21000 22000 24000
Extra Revenue 1000 2000 3000 1000 2000 2000 1000 1500 2500
Costs 500 1000 2000 500 1000 1000 750 1000 1000
Profit/Loss 10500 13000 15000 16500 12000 21000 21250 22500 25500
ZygD
  • 22,092
  • 39
  • 79
  • 102
Automation Monkey
  • 355
  • 1
  • 8
  • Can you provide some sample data plus expected output - preferably via [Markdown Table Generator](https://www.tablesgenerator.com/markdown_tables) – Ike Sep 26 '22 at 14:12
  • hey Ike, I have used the generator, I'm not sure if that's what you're looking for though? The ByCol function would be in the first cell of the Profit/Loss and auto calculate for the length determined by D2 in my case. – Automation Monkey Sep 26 '22 at 14:22
  • And where is row 10 in your sample data? What is the value of D2? – Ike Sep 26 '22 at 14:25
  • Revenue will be row 4, extra revenue row 5 and costs row 10. D2 can be variable but atm is 25. So to adjust the function for the sample data above you'd have `=BYCOL((INDEX($1:$11,{2;3;4},SEQUENCE(1,4,COLUMN(I:I)))),LAMBDA(x,y,z,SUM((x+y)-z)))` assuming SUM((x+y)-z) actually worked :) – Automation Monkey Sep 26 '22 at 14:36

2 Answers2

4

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.

enter image description here

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})))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
2

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

enter image description here

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

enter image description here

=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.

Ike
  • 9,580
  • 4
  • 13
  • 29