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


    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))


    I guess this is the other thread you were referring to, now with an alternate solution: http://www.ozgrid.com/forum/showthread.php?t=196006

    Where there is a will there are many ways. Finding one that works for you is the challenge!

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



    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

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


    Yes that is correct. Do you have values in all the relevant references? Do you have to expand the BD2:BM2 and other ranges to suit? All ranges should be same size

    Where there is a will there are many ways. Finding one that works for you is the challenge!