+ Reply to Thread
Results 1 to 3 of 3

INDIRECT/ADDRESS inside LINEST function versus inside INTERCEPT

  1. #1
    Registered User
    Join Date
    06-18-2013
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    5

    INDIRECT/ADDRESS inside LINEST function versus inside INTERCEPT

    Having trouble figuring out why this would work for LINEST, but not for INTERCEPT, RSQ or STEYX, given they all have the same parameters and format. Additionally, I've tried all of those formulas in the exact same cell as the LINEST one that is working.

    For background, the objective is to run multiple regressions against live data (being pushed externally to excel via an Excel Add-In API) on one sheet to act as a monitor.

    The columns for both X's & Y's are dependent on numbers (for start column and end column) entered into cells E19 & E20 by the user.

    EDIT: The rows for X's and Y's are dictated by formulas which match a value in column S to values in A23:A48 (for X's) and the row of the cell in question for Y's.

    Formula below:

    Please Login or Register  to view this content.
    EDIT: The address combos in the first INDIRECT() are "V24:EX24" and for the second INDIRECT() are "V23:EX23"


    Any help much appreciated!
    Last edited by slny06; 01-11-2019 at 02:12 PM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,582

    Re: INDIRECT/ADDRESS inside LINEST function versus inside INTERCEPT

    Have you tried using an INDEX approach, rather than INDIRECT?

    If I've read your INDIRECT approach correctly the INDEX equivalent would be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    assuming you get the same results you could apply similar logic to the other functions to see if they then work as expected?

    the INDEX approach still relies on implicit precedents but it would no longer be volatile, and it might be the latter that is causing issues with your other Function calls - I'm afraid I've no experience using them myself.
    Last edited by XLent; 01-14-2019 at 08:12 AM.

  3. #3
    Registered User
    Join Date
    06-18-2013
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: INDIRECT/ADDRESS inside LINEST function versus inside INTERCEPT

    This worked perfectly. Thanks for the solution!

+ 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] Changing Cell References inside INDIRECT function.
    By Zox Tomana in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-08-2016, 12:36 AM
  2. Using Indirect function inside the Search function
    By skhari in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2014, 05:32 PM
  3. [SOLVED] Need IF statement (inside of Linest Function) that will select range based on dates
    By cpkrehbiel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-07-2014, 09:22 PM
  4. INDIRECT function not working inside MATCH with dynamic ranges
    By fotografer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2014, 02:09 AM
  5. INDIRECT inside a LINEST function
    By Glenn Kennedy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-24-2013, 12:06 PM
  6. [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
  7. ADDRESS use inside a function
    By aries in forum Excel General
    Replies: 5
    Last Post: 02-09-2010, 07:21 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