Using LINEST with Conditional Arrays

[JTWood]

Summary Question
I am trying to use LINEST to calculate the linear regression output for selected data points in a table. The selection of the data points is being determined by a conditional array. Here's an example of the formula:

LINEST(IF(D2:D10000=2,E2:E10000),IF(D2:D10000=2,E2:E10000))
It returns a #VALUE response.

This method works great for other functions, like STDEV.S(IF(D2:D10000=2,E2:E10000)) or AVERAGE(IF(D2:D10000=2,E2:E10000)), but not for LINEST.
Is this simply not possible for LINEST? Or do I need to consider other steps to make it work?

Additional Detail
The data set is hourly sums of metrics for an extended period of time. I need to compare the exact hour of the day for the entire time frame for one metric to the exact hour of the day for the entire time frame for another metric. Here's a screenshot of what the data basically looks like:

In this particular case, I highlighted just to show the potential values that should be included in the LINEST.

 

[JTWood]

Never mind. I'm reading now that you cannot use conditional arrays in LINEST. It doesn't like the 'FALSE' responses.

 

[Domenic]

I'm not a statistician, but I think that in your example Column E is the "Known y" and Column F is the "Known x", correct? If so, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=LINEST(N(OFFSET(E2:E10000,SMALL(IF(D2:D10000=2,ROW(D2:D10000)-ROW(D2)),ROW(INDIRECT("1:"&COUNTIF(D2:D10000,2)))),0,1)),N(OFFSET(F2:F10000,SMALL(IF(D2:D10000=2,ROW(D2:D10000)-ROW(D2)),ROW(INDIRECT("1:"&COUNTIF(D2:D10000,2)))),0,1)))

Also, can you use the SLOPE and INTERCEPT functions to return the same results as LINEST? If so, try the following formulas instead...

=SLOPE(IF(D2:D10000=2,E2:E10000),IF(D2:D10000=2,F2:F10000))

=INTERCEPT(IF(D2:D10000=2,E2:E10000),IF(D2:D10000=2,F2:F10000))

Note that both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[JTWood]

I'm not a statistician, but I think that in your example Column E is the "Known y" and Column F is the "Known x", correct? If so, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=LINEST(N(OFFSET(E2:E10000,SMALL(IF(D2:D10000=2,ROW(D2:D10000)-ROW(D2)),ROW(INDIRECT("1:"&COUNTIF(D2:D10000,2)))),0,1)),N(OFFSET(F2:F10000,SMALL(IF(D2:D10000=2,ROW(D2:D10000)-ROW(D2)),ROW(INDIRECT("1:"&COUNTIF(D2:D10000,2)))),0,1)))

Also, can you use the SLOPE and INTERCEPT functions to return the same results as LINEST? If so, try the following formulas instead...

=SLOPE(IF(D2:D10000=2,E2:E10000),IF(D2:D10000=2,F2:F10000))

=INTERCEPT(IF(D2:D10000=2,E2:E10000),IF(D2:D10000=2,F2:F10000))

Note that both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hey, thanks. All those options worked great. I had found a "How to work around not using LINEST" page and bookmarked it for later.

Pretty sure you just saved me a good bit of reading!

 

[XOR LX]

Just a point, but I prefer the non-volatile and shorter:

=LINEST(INDEX(E:E,N(IF(1,MODE.MULT(IF(D2:D10000=2,{1,1}*ROW(D2:D10000)))))),INDEX(F:F,N(IF(1,MODE.MULT(IF(D2:D10000=2,{1,1}*ROW(D2:D10000)))))))

for the LINEST construction.

Regards