MS Excel - SumProduct formula with Loop

0 votes

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.

Mar 10, 2022 in Database by Edureka
• 13,690 points
573 views

1 answer to this question.

0 votes

Drag this to the right of cell b6 

=SUM

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

In an Excel sheet, you have columns for Items, Quantity, and Price. For each row, you'll need the total following the (Quantity * price) columns, as well as the grand total.

The SUMPRODUCT function multiplies and returns the sum of corresponding arrays or ranges. To calculate the grand total in the aforementioned scenario, use the formula:

=SUMPRODUCT(A2:A6,B2:B6)

answered Mar 14, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

how do I calculate discount with if function in excel?

Suppose a customer gets a 10 percent ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
11,028 views
0 votes
0 answers

If Cell Starts with Text String... Formula

I have formula that checks if the ...READ MORE

Feb 24, 2022 in Database by Edureka
• 13,690 points
1,548 views
0 votes
1 answer
0 votes
1 answer

Excel VBA - exit for loop

When we need to depart the For ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
785 views
0 votes
1 answer

Prevent cell numbers from incrementing in a formula in Excel

In Excel, you can use a feature ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
9,810 views
0 votes
1 answer

Excel 2003 - Match Column A with Column B & Column C to correspond to Column B

Write a standard IF formula that compares ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
2,856 views
0 votes
1 answer

Write formula to Excel with Python

Using python to add formulas to an ...READ MORE

answered Mar 31, 2022 in Database by gaurav
• 23,260 points
3,822 views
0 votes
1 answer

Excel formula for greater than but less than with several tiers

Use this: =MATCH(A1,{0,35,60,85,110}) READ MORE

answered Apr 5, 2022 in Database by gaurav
• 23,260 points
6,746 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP