# nested sumproduct + offset

#### BruceBayer

##### New Member
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)

 A B C D E F G H I J K L M N O 1 Period 1 2 3 4 5 6 7 8 9 10 11 12 13 2 Invested 3 4 5 0 0 0 0 0 0 0 0 0 0 3 Returns 15 35 60 60 60 60 60 60 60 60 45 25 4 5 Period 1 2 3 4 5 6 7 8 9 10 6 Return per invested \$ 5 5 5 5 5 5 5 5 5 5

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

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

Replies
1
Views
47
Replies
5
Views
78
Replies
10
Views
233
Replies
2
Views
143
Replies
1
Views
73