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

Once you solve whatever the immediate problem is, that will pass interleaved values of False and numbers to LINEST, which it will not be happy with. Some function ignore non-number values; LINEST throws up.

I don't think you're getting it.

If the source data didn't contain values >= 200, there would be no need for the IF function. If it does, then the input to LINEST will be numbers interleaved with False, which won't work.

Also not sure why you are using INDEX() to return just the first value.

Using the INDEX function like that is intended (I hope), for information (as in "Yup, that's the way they come out"), not direction (as in "Here's how you should do it").

LINEST returns all of the coefficients in a single array formula. Extracting them one by one using INDEX means you repeat the entire regression -- which is non-trivial -- for every coefficient.