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:


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


    you get the incorrect formula


    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