Results 1 to 3 of 3

Thread: cell reference?

  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    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.
    nevertheless, thanks in advance for whatever help you can provide me.
    gratefully,

    danielR2
    Attached Files Attached Files

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Don't know if this answers your question.
    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]
    Attached Files Attached Files
    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by AKW View Post
    Don't know if this answers your question.
    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;

    Thank you very much for your reply and your efforts.
    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
  •