Nested array formula for a SUMPRODUCT operation

  • I have two columns of data with N data each, being N variable. Be data in column 1: {a1, a2, ....., aN} and in column 2: {b1, b2, .... bN}. Each figure is in one cell
    [TABLE="width: 500"]

    [tr]


    [td]

    a1

    [/td]


    [td]

    b1

    [/td]


    [/tr]


    [tr]


    [td]

    a2

    [/td]


    [td]

    b2

    [/td]


    [/tr]


    [tr]


    [td]

    ....

    [/td]


    [td]

    ....

    [/td]


    [/tr]


    [tr]


    [td]

    ....

    [/td]


    [td]

    ....

    [/td]


    [/tr]


    [tr]


    [td]

    aN

    [/td]


    [td]

    bN

    [/td]


    [/tr]


    [/TABLE]


    I need an array formula to be able to perform the following calculation:
    a1*(1+b1)*(1+b2)*...*(1+bN)+a2*(1+b2)*(1+b3)*...*(1+bN)+.....+aN*(1+bN)


    It needs to be an array formula because the arrays in columns 1 and 2 are of different length every time. Imaging data in columns A and B from rows 1 to 10 (N=10), the formula I tried was:
    {=SUMPRODUCT(A1:A10,PRODUCT(1+B1:B10))}
    but it didn't work

  • Re: Nested array formula for a SUMPRODUCT operation


    Crossposted. Please post all links to other forums where you posted this question.

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


    MS Excel MVP 2010-2016