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