1. hello everyone
i need some help with (i believe), some referencing in Excel. i am attaching a spreadsheet illustrating my problem:
from some other part of the sheet (not shown here), a variable length table is created in columns C & D according to some other data entered elsewhere.
in this case, the column has 16 rows, but can be more, or can be less.
i need the formulas in cells E2 and F2 to perform the calculations automatically regardless of the length of table in columns C and D (when i enter that data elsewhere)
i don't know how (or what) to enter in the range references which (in this example) point to B17 and C17.
if possible, i would like to help / suggestions pointing to the use of an already standard Excel function, so i will be learning the use of something new; instead of a specific VBA program which addresses this situation.
gratefully,

danielR2

Depends if there is anything else below Columns B,C and D Other than an data list that increases in size

I used the Following 2 Formula to Build the Dynamic Ranges

Using COUNT to get a count of cells with a number in
Using OFFSET to generate the data range

Basic Offset Function is =OFFSET(StartCell,RowOffset,ColOffset)
But IF you are passing the results to a function that accepts a range you can also use
=OFFSET(StartCell,RowOffset,ColOffset,Size In Rowss,Size in Columns)

PV Calc =(OFFSET(\$B1,COUNT(\$B2:\$B10000),0))/(1+E8/365)^((OFFSET(\$C1,COUNT(\$C2:\$C10000),0)-TODAY()))

XNPV Calc =XNPV(\$A\$2,OFFSET(\$B1,1,0,COUNT(\$B2:B10000),1),OFF SET(\$C1,1,0,COUNT(\$C2:C10000),1))

Not sure what E8 does since there is NO value in E8

Formulas also in attached sheet

[attachment=88527:Xmple 4 Woody.xls]

3. Originally Posted by AKW
Depends if there is anything else below Columns B,C and D Other than an data list that increases in size

I used the Following 2 Formula to Build the Dynamic Ranges

Using COUNT to get a count of cells with a number in
Using OFFSET to generate the data range

Basic Offset Function is =OFFSET(StartCell,RowOffset,ColOffset)
But IF you are passing the results to a function that accepts a range you can also use
=OFFSET(StartCell,RowOffset,ColOffset,Size In Rowss,Size in Columns)

PV Calc =(OFFSET(\$B1,COUNT(\$B2:\$B10000),0))/(1+E8/365)^((OFFSET(\$C1,COUNT(\$C2:\$C10000),0)-TODAY()))

XNPV Calc =XNPV(\$A\$2,OFFSET(\$B1,1,0,COUNT(\$B2:B10000),1),OFF SET(\$C1,1,0,COUNT(\$C2:C10000),1))

Not sure what E8 does since there is NO value in E8

Formulas also in attached sheet

[attachment=88527:Xmple 4 Woody.xls]

Hi Andrew;

I will try it this coming weekend (I would like to read and learn about the functions you are introducing), and report to you accordingly.

Best regards,

danielr2.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•