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.
 

Forum statistics

Threads
1,082,635
Messages
5,366,687
Members
400,913
Latest member
SarahMS1

Some videos you may like

This Week's Hot Topics

Top