+ Reply to Thread
Results 1 to 6 of 6

INDIRECT inside a LINEST function

  1. #1
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    INDIRECT inside a LINEST function

    The attached sheet shows where I'm at...

    I am using a standard LINEST array to calculate some parameters that aren't delivered by a simple regression. The data for the LINEST are on a Table, which are (in turn ) copied from another table in the same workbook. However, the data can have some blank rows at the bottom. So, I need to make the range for the LINEST dynamic or I need to use INDIRECT to pick up the address of the last row with data in it.

    I know absolutely nothing about VBA, so I need to crack it with INDIRECT. However, I can't get the syntax right. I'm trying to pick up the row number of the last data containing row from cell I2 (Attempt 1) , or the address of the last X and Y from cells I8 and I5, respectively.

    What am I doing wrong? If you can reproduce the numbers that are present in the "traditional LINEST" bit, then I guess you've got it working !

    Hopefully this isn't too difficult, but I can't spot what's wrong. Good luck, 'cos it's doing my head in.
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: INDIRECT inside a LINEST function

    Please Login or Register  to view this content.
    not G2&":G"&I2 etc....

    and
    Please Login or Register  to view this content.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: INDIRECT inside a LINEST function

    Kaper, a really big thank you !! I'll try this out on the main sheet tomorrow, but this looks perfect for now. If all's OK, I close it in the morning.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: INDIRECT inside a LINEST function

    Like this...

    =LINEST(G2:INDEX(G2:G9,MATCH(1E100,G2:G9)),F2:INDEX(F2:F9,MATCH(1E100,G2:G9)),TRUE,TRUE)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: INDIRECT inside a LINEST function

    Many thanks, both. This web site is amazing. There's always someone who's been there before & who's prepared to help.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: INDIRECT inside a LINEST function

    You're welcome. Thanks for the feedback!

    If your question has been answered please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools>Mark this thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Nesting 2 INDIRECT inside an INDEX MATCH - any ideas?
    By Sam the Monster in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-14-2013, 10:25 AM
  2. [SOLVED] Linest Function - Unable to get LinEst property of the WorksheetFunction class
    By fbs13 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-25-2013, 09:55 AM
  3. Using Indirect inside of Range
    By Jumper1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-12-2013, 01:23 PM
  4. [SOLVED] Using ROW function inside of both an Indirect and Index function returns #VALUE
    By xandermacleod in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-11-2012, 06:22 PM
  5. Using INDIRECT in INDEX(LINEST.. ) function
    By Incoherent in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-10-2006, 12:45 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1