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 359 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
• 23,260 points

0 votes
1 answer

## Reading Excel File using Python, how do I get the values of a specific column with indicated column name?

So the key parts are to grab ...READ MORE

0 votes
1 answer

## Excel - Split cell with a delimiter and if delimiter not found get complete cell value to another cell

Select the row or column that you ...READ MORE

0 votes
1 answer

## how do I calculate discount with if function in excel?

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

0 votes
0 answers

## If Cell Starts with Text String... Formula

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

0 votes
1 answer

## Reading Excel File using Python, how do I get the values of a specific column with indicated column name?

We can read excel more easily with ...READ MORE

0 votes
1 answer

## Excel VBA - exit for loop

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

0 votes
1 answer

## Prevent cell numbers from incrementing in a formula in Excel

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

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

0 votes
1 answer

## Write formula to Excel with Python

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

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