Excel single cell calculation for the sum of a series of compounding products
Posted on 2014-08-26
I would like to calculate the sum of a series of compounding products in a single cell. I can do this rather simply in excel by calculating the series of compounding products in a table, then summing each column of the resulting products. However, I would like to do this in a single cell, and without any VBA. Arrays would be fine.
The original data is organized as follows:
In a single row, there are multiple columns of fractions. After the 25th colum of data, the data changes from being displayed in a single row to being displayed down the 26th column. So the data is in a downward "L" shape in the table. Assume the data is in ROW 1 from COLUMN A to COLUMN Z, then continuing down COLUMN Z to ROW 30.
To do the calcualtion in multiple steps, I would calculate the compounding products as follows in a single row below the table:
First, remove the "L" shape in the table by copying the table to a new location, and transposing the data in the 26th column and appending it to the end of the ROW 1.
Then in cell A40 calculate, (1-A1), then in B40 calculate A40*(1-B1), and drag that formula accross the row to the end of the columns containing data.
Then in cell A45 calculate SUM(A40:[end of columns with data]). This is the final result I am looking for.
I woud like to calcualte all of this in a single cell and without having to "straighten out" the "L" shape in the original table.