Search code examples
arraysexcelexcel-formulaexcel-2010sumifs

MS Excel - SumProduct formula with Loop

I have 4 arrays of data where I need a some product but with few conditions.

I'm unable to solve that and I'm not good at creating VBA functions as well.

Can anyone please help?

Apologies for the lack of clarity.

I'll try to give more details here. Please refer the below workbook.

https://drive.google.com/file/d/1XVEe4Sjw6ZeAh-7jyeLDs7Tp5RRQD20i/view?usp=sharing

Investment value is Row 2 * Row 3. This value is carried across all the months after amortization given in row 4. 5th row is converting the yearly value into monthly value (1/12).

So, in the first month 50 is invested and the value at the beginning of the month after amort of 100%, So 50 is available for month 1. This is further converted into monthly values with the help of row 5. So, the result for month 1 will be 100 * 50% * 100% * 8.33% = 4.1667 [Cell I9]

For the month 2, the value available is,

Value carried from month 1 after amort (100 * 50% * 99%) + New amount in month 2 (102 * 50% * 100%)

The above value is converted into the monthly value again with the help of row 5.

Result for month 2 => Cell I10 = (100 * 50% * 99% * 8.33%) + (102 * 50% * 100% * 8.33%) = 8.375

Similarly, for month 3 the value will be,

Month 1's investment (100*50%*98%*8.33%) + Month 2's investment (102*50%*99%*8.33%) + Month 3's investment (104*50%*100%*8.33%)

I need the above calculation to happen in single cell for all the months. I tried sumproduct with offset. But I couldn't.

Solution

  • REVISED (based upon comment 'all values are dynamic)...

    Plug this into cell b6 per screenshot, drag to right:

    =SUM($B2:B2*$B3:B3*$B4:B4*$B5:B5)

    This will evaluate the sums you have indicated below the 24 month table [at top your linked spreadsheet] i.e. which correspond to respective months 1,2,... etc.

    Application of 'single-cell' function that caters for 'fully dynamic parameters' within the table.

    PREVIOUS SOLN:

    HIGH LEVEL

    Let cell A1 contain the month (=π‘Ÿ, π‘Ÿ πœ– β„•). Plug this into excel to get the sum of the π‘Ÿ terms for k = 1,...,π‘Ÿ:

    =A1*(98-A1+(3/2)*(1+A1)+(1/50)*((1+A1)*((2*A1+1)/6-A1/2-1/2)+A1))/24

    DERIVATION

    Your sequence has π‘Ÿ terms; let 𝑑(π‘˜) be the π‘˜th such term:

    𝑑(π‘˜) = (100+2(π‘˜βˆ’1))(1βˆ’(π‘Ÿβˆ’π‘˜)/100)/24

    where 1 < 2 < ,..., ≀ π‘˜ ≀ ,.., π‘Ÿ. Let 𝑆(π‘Ÿ) be the sum over π‘˜ = 1, ... , π‘Ÿ. It immediately follows that:

    𝑆(π‘Ÿ)= (1/24) βˆ‘(100+2(π‘˜βˆ’1))(1βˆ’(π‘Ÿβˆ’π‘˜)/100); π‘Ÿ πœ– β„•; π‘˜ = 1,2,..,π‘Ÿ

    Elementary simplification yields the following:

    𝑆(π‘Ÿ) = (1/24) π‘Ÿ { 98 - π‘Ÿ + 1.5 (1 + π‘Ÿ) + 0.02 {(1+π‘Ÿ)((2π‘Ÿ+1)/6 - π‘Ÿ/2 - 1/2) +r } }

    (𝑄𝐸𝐷 ☻)

    CHECKS

    π‘Ÿ (A1) Result Status
    1 4.17 √
    2 8.38 √
    3 12.62 √
    4 16.91 √
    5 21.24 √
    .. .. n/a
    24 109.81 √