Using LINEST function with conditions

Formulas...

F6 must house a 0.

F8, copied down:

=IF((D8>=1)*(E8>=1),LOOKUP(9.99999999999999E+307,$F$6:F7)+1,"")

G6:

=LOOKUP(9.99999999999999E+307,F6:F18)

G8, copied across to H8 then down:

=IF(ROWS(G$8:G8)<=$G$6,LOOKUP(ROWS(G$8:G8),$F$8:$F$18,D$8:D$18),"")

J8:

=LINEST(G8:INDEX(G8:G18,G6),H8:INDEX(H8:H18,G6))

Addendum:

=INDEX(LINEST(G8:INDEX(G8:G18,G6),H8:INDEX(H8:H18,G6)),2)

Try the following...

F7: enter 0

F8, copied down:

=IF((D8<>"")*(E8<>""),IF(D8>=1,LOOKUP(9.99999999999999E+307,$F$7:F7)+1,""),"")

G7:

=LOOKUP(9.99999999999999E+307,F8:F18)

H8, copied down and over to Column I:

=IF(ROW()-ROW(H$8)+1<=$G$7,LOOKUP(ROW()-ROW(H$8)+1,$F$8:$F$18,D$8:D$18),"")

Define the following names...

Insert > Name > Define

Name: Array1

Refers to:

=Sheet1!$H$8:INDEX(Sheet1!$H$8:$H$18,Sheet1!$G$7)

Click Add

Name: Array2

Refers to:

=Sheet1!$I$8:INDEX(Sheet1!$I$8:$I$18,Sheet1!$G$7)

Click Ok

*Make sure that you change the sheet reference accordingly.

Then, use the following formula...

=INDEX(LINEST(Array1,Array2),2)

Hope this helps!

Edit: Changed the formulas for the defined names as per Aladin's solution. Nice!

Dave,

I noticed that Aladin's solution eliminates any row where Column D or Column E does not contain a value greater than or equal to one. However, in your example, you only eliminate a row if Column D does not contain a value greater than or equal to one. My solution is based on your example. If, as I suspect, Aladin has it right, change my formula for F8 to the following...

=IF((D8<>"")*(E8<>""),IF((D8>=1)*(E8>=1),LOOKUP(9.99999999999999E+307,$F$7:F7)+1,""),"")

How would you apply similar conditions to data that crosses thresholds. I want to include only the cells that have satisfied condition of n>30 threshold in the LINEST formula.

Ideally, I would have one array selected for the LINEST and I would not have to manually increase/decrease the cells used for the function as my column thresholds are crossed.