Results 1 to 8 of 8

Using IF-based array formula within INDEX(LINEST()) formula

Threaded View

  1. #1
    Registered User
    Join Date
    12-21-2012
    Location
    Boston, MA, USA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Using IF-based array formula within INDEX(LINEST()) formula

    Hey folks,

    Trying to get this formula to work. I have it working on my sheet when I use SLOPE() and INTERCEPT(), but since I am calculating the equation for a 3rd order polynomial, those won't do.

    Here is my formula:
    =INDEX(LINEST(IF('Backend Analysis'!$AC$3:$AC$367<200,'Backend Analysis'!$AC$3:$AC$367),IF('Backend Analysis'!$AC$3:$AC$367<200,'Backend Analysis'!$D$3:$D$367)^{1,2,3}),1)

    The problem is that I am getting the same answer as if I enter it without the IF-based array filter, which is this equation:
    =INDEX(LINEST('Backend Analysis'!$AC$3:$AC$367,'Backend Analysis'!$D$3:$D$367^{1,2,3}),1)

    The spirit of the formula is to only use values from the AC array and the D array where the value in that same row of the AC array is < 200 (200 is a stand in, this will ultimately be a dynamic formula, but if I can get it working with a hardcoded number, it's easy enough to do the rest)

    I suspect it has something to do with the fact that linest, itself, is an array formula -- just not sure though. Any help would be much appreciated!!

    T
    Last edited by thalsted; 09-22-2015 at 01:05 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index & Array Formula
    By Tim204030 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-24-2015, 11:47 AM
  2. [SOLVED] Help with Array and index Formula
    By shayej in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-26-2015, 06:22 PM
  3. Sum with array formula in index
    By Matthys.Steyn in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-17-2013, 10:44 AM
  4. Replies: 2
    Last Post: 06-20-2012, 12:22 PM
  5. LINEST array formula not working
    By mouseclicker in forum Excel General
    Replies: 2
    Last Post: 10-12-2010, 08:29 PM
  6. Index array formula?
    By kdestef1 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-11-2009, 10:56 AM
  7. SUM of INDEX as array formula
    By CJEJung in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-21-2008, 01:15 PM

Tags for this Thread

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