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.

Apologies for the lack of clarity.

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

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 360 views

## 1 answer to this question.

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)

• 23,260 points

## 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

## 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

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

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

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

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

## Excel VBA - exit for loop

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

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

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

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

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