nested sumproduct + offset

BruceBayer

New Member
Joined
Oct 5, 2015
Messages
1
I'm trying to understand how the offset() , n() , and offset() functions are used here to adjusted the sumproduct range. I've created a table below to show the spreadsheet. The formula in cell D3 is applied across the row
D3=+SUMPRODUCT(N(OFFSET($B$2:C2,,C1-COLUMN($B$2:C2)+2)),$C$6:D6)

E3=+SUMPRODUCT(N(OFFSET($B$2:D2,,D1-COLUMN($B$2:D2)+2)),$C$6:E6)

ABCDEFGHIJKLMNO
1Period12345678910111213
2Invested3450000000000
3Returns153560606060606060604525
4
5Period12345678910
6Return per invested $5555555555

<tbody>
</tbody>


Year 1, $3 invested, Year 2 $4 invested and Year 3 $5 invested. Each dollar buys a 10year annuity of $5 per year. So using the formula to aggregate the total payments over the life of the annuities.

Thanks for the help in understanding how the formula works.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Threads
1,118,174
Messages
5,570,684
Members
412,336
Latest member
Tiffany927
Top