Linest of rows matching criteria

**RodGob** · 12-11-2013, 05:38 PM

Hi,

I've been trying to use Linest function in a way that it will act only on the rows that match a specific criteria. The table has >1800 rows and 3 columns. First column has an identifier value, second column is the known_x's, and the third column would be for the known_y's. The identifier would match the criteria, which has >230 different numbers. The following table shows a bit of what I have:

ID	434	434	434	434	434	435	435	435	435	435	436	436	436	436
NumPair	1	2	3	4		1	2	3	4		1	2	3	4
InstantFrequency	0.032	0.043	0.053666667	0.055		0.035333333	0.039	0.045666667	0.049666667		0.018666667	0.021	0.022666667	0.023666667
Criteria	434	435	436

(sorry for the crappy format)

So, I'd like to obtain the m value and the y interception from each group of number (434, then 435, then 436). I tried a few things with MATCH and INDEX but without success.
If anyone has any idea of what I could do, it would be great!

Cheers!

**RodGob** · 12-12-2013, 05:50 AM

I actually found out a way of solving my problem. I'll post it here in case anyone else needs something similar.

In one extra column (H), I added the following equation, to be the start of the search:
=MATCH(G2,$A$1:$A$1830,0)

Another column (I) has the end of the search:
=MATCH(G3,$A$1:$A$1830,0)-2

Then I used Address nested inside of Indirect to force Linest to get the values I wanted:
{=LINEST(INDIRECT(ADDRESS(H2,5,4)&":"&ADDRESS(I2,5,4)),INDIRECT(ADDRESS(H2,4,4)&":"&ADDRESS(I2,4,4)))}

It seems that Address on its own doesn't return as a cell reference, therefore the use of Indirect.

I hope this helps anyone else.