Array formulae question

[Robert Davidson]

I am looking for some help with an array formula. In cells P3 to Y3 I have the following formula which works perfectly:

=LET(yr,SEQUENCE(ModDurn,,StYr),
cost1,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2,OR(y<=D14,D14=0)),D11*(NPE+NPMY1),0))),0),
cost2,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2+1,OR(y<=D14,D14=0)),D11*NPMY2,0))),0),
cost3,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2+2,OR(y<=D14,D14=0)),D11*NPMY3,0))),0),
cost4,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2+3,OR(y<=D14,D14=0)),D11*NPMY4,0))),0),
sfcost,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2,OR(y<=D14,D14=0)),D3*SF,0))),0),
dfcost,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2,OR(y<=D14,D14=0)),D4*DF,0))),0),
ofcost,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2,OR(y<=D14,D14=0)),D5,0))),0),
HSTACK(cost1+cost2+cost3+cost4+sfcost+dfcost+ofcost))

I am totalling these columns in cell Z3 with this formula which again works perfectly:

=HSTACK(P3#+Q3#+R3#+S3#+T3#+U3#+V3#+W3#+X3#+Y3#)

I would like to change the first formula to introduce another calculation of the same information (adding in dcost1 to dofcost - see below) and summarise that in a new total under NPV total (column AA) similar to the HSTACK above.

How do I get the sum of the second section of the first formula (from dcost1 to dofcost) into a new column under NPV total (column AA)?

 

[Fluff]

You will need to put the 2nd part of the formula as a new formula in col AA.
Also the Hstack functions you are using are not needed.

 

[Robert Davidson]

You will need to put the 2nd part of the formula as a new formula in col AA.
Also the Hstack functions you are using are not needed.

Yes that would work although the first formula would become very large to accommodate all 20 columns. Perhaps it's not clear from the screenshot but each column from P to Y refers to different columns elsewhere from D to W.

 

[Fluff]

The first formula needs to remain as you posted (although without the hstack which is not doing anything). The formula in AAA would just be 2nd part of the formula you showed in the image.

 

[Fluff]

It also looks as though you could simplify the original formula like

=LET(yr,SEQUENCE(ModDurn,,StYr),
cost1,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2,OR(y<=D14,D14=0)),(D11*(NPE+NPMY1))+(D3*SF)+(D4*DF)+D5,0))),0),
cost2,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2+1,OR(y<=D14,D14=0)),D11*NPMY2,0))),0),
cost3,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2+2,OR(y<=D14,D14=0)),D11*NPMY3,0))),0),
cost4,IFERROR(-BYROW(yr,LAMBDA(y,IF(AND(y=D2+3,OR(y<=D14,D14=0)),D11*NPMY4,0))),0),
cost1+cost2+cost3+cost4)

 

[Robert Davidson]

Yes that helps on both accounts thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Robert Davidson]

I have found another solution to this problem which does not involve using the second part of the formu:a in the original image above: