# Linest of rows matching criteria

1. ## Linest of rows matching criteria

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!

2. ## Re: Linest of rows matching criteria

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:

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.

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