Results 1 to 2 of 2

Thread: Array Formula Help Needed

  1. #1

    Array Formula Help Needed



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi I need help producing an array formula that will produce the following results when entered in B3 and dragged across to D3:

    A B C D
    1 Amount 1,000 0 1,000
    2 % Rate 1% 2% 3%
    3 Formula =(B1*B2) =((B1*(1+B2))*C2)+(C1*C2) =((B1*(1+B2)*(1+C2))*D2)+((C1*(1+C2))*D2)+(D1*D2)

    I have an array formula which is nearly working:

    {=SUM(OFFSET($B1,0,0,1,COLUMNS($B$1:B$1))*IF(COLUMNS($B$1:B$1)>1,PRODUCT(1+OFFSET($B2,0,0,1,COLUMNS($B$1:B$1)-1)),1)*B2)}

    The issue is that the above formula is applying the same compound interest growth to all amounts rather than the historic amounts...so in D3 instead of getting the correct result of:

    =((B1*(1+B2)
    *(1+C2))*D2)+((C1*(1+C2))*D2)+(D1*D2)

    you get the incorrect formula

    =((B1*(1+B2)*(1+C2))*D2)+((C1*(1+B2)*(1+C2))*D2)+((D1*(1+B2)*(1+C2))*D2)


    I logically know how this should be written mathematically BUT cannot figure it out in excel...

    Assuming we are solving for D3 - I need to be able to do the following:

    Amount = Array X = {1000,0,1000}
    % Rate = Array Y = {0.01,0.02,0.03}

    Results = ((X1*(1+Y1)*(1+Y2)) + (X2*(1+Y2)) + X3)*Y3

    Any ideas? Or ways of reconciling the result by subtracting some other formula...

    THANKS SO MUCH for any and all help.

  2. #2
    Please see the attached file as example of above problem.
    Array Formula Help.xlsx

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •