Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Define Name Function (97 SR1)

    Hello,

    I am looking for a Define Name equation that will allow me to ignore the empty rows in my X- and Y- ranges. Those ranges will be used in the LINEST function which does not accept empty cells. An example spreadsheet is attached.

    Thanks for your help,
    Hanan.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Define Name Function (97 SR1)

    Instead of using the ranges create range names like this:
    insert -name - define
    Name: XRange
    refers to:
    =OFFSET(Sheet1!$B$6,0,0,COUNT(Sheet1!$B$6:$B$20),1 )
    <add>
    Name: YRange
    Refers to:
    =OFFSET(Sheet1!$C$6,0,0,COUNT(Sheet1!$C$6:$C$20),1 )
    <OK>

    Then use (eg)
    =INDEX(LINEST(YRange,XRange^{1,2},FALSE,TRUE),1,3)

    Steve

Posting Permissions

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