An array excel formula to increase the numebrs in the array bracket

Hi guys,

I had an excel formula that contained nine exponentials, the formula was initially complicated but it was shortened with an array of 9 years.
this is the old formula. they were two formulas

Old
=SUMPRODUCT((1-0.035)^{0,1,2,3,4,5,6,7,8,9},BD2:BM2-AF2:AO2+0.28*AR2:BA2/10^6*8.61)

=IF(BP2=0,0,IF(BP2>0,"N/A",SUMPRODUCT((1-0.035)^{0,1,2,3,4,5,6,7,8,9},AR2:BA2)/10^6*8.61/(BP2*-1))).

I have now entered several columns in the excel spreadsheet to change it from 9yrs to 40 years, so I need an array of 40 years and It seems a bit complicated to enter the bracket (1,2,3,4 ..............40)

this the new formula with the added columns but not with the 40 arrays added.

New
=SUMPRODUCT((1-0.035)^{0,1,2,3,4,5,6,7,8,9},CG22:DT22-C22:AP22+0.28*AR22:CE22/10^6*8.61)

=IF(FK22=0,0,IF(FK22>0,"N/A",SUMPRODUCT((1-0.035)^{0,1,2,3,4,5,6,7,8,9},AR22:CE22)/10^6*8.61/(FK22*-1)))

Can someone please advise how I can change the number of years from 9 to 40 without entering the (1,2 .... till 40)

I have posted this in a previous thread as a reply, am trying to link it but am having issues doing that, so apologies for the inconvenience

Many thanks

Sarah

Re: An array excel formula to increase the numebrs in the array bracket

Quote from NBVC;773276

Try replacing {0,1,2,3,4,5,6,7,8,9}

with

(ROW($A$1:$A$10)-ROW($A$1))

for 0-40 it would be:

(ROW($A$1:$A$41)-ROW($A$1))

Display More

many thanks for the above message, is this what you mean because I am getting an error

=SUMPRODUCT((1-0.035)^(ROW($A$1:$A$41)-ROW($A$1)),BD2:BM2-AF2:AO2+0.28*AR2:BA2/10^6*8.61)

KR

Sarah