Using LINEST function with conditions

[Dave L]

I have two columns of data (D & E) and the following equation works fine to calculate a straight line that best fits my data:

INDEX(LINEST($D$8:$D$18,$E&8:$E$18),2)

However, I need to calulate it only using data that meets a certain criteria (>=1). I tried the following, but got a "VALUE" error:

INDEX(LINEST(IF($D$8:$D$18>=1,$D$8:$D$18),IF($D$8:$D$18>=1,$E&8:$E$18)),2)

Any help would be greatly appreciated.

Thanks,

Dave

 

[Aladin Akyurek]

Book2
	C	D	E	F	G	H	I	J
6				0	7
7		X1	X2	Idx	*X1	*X2
8		1.2	2.3	1	1.2	2.3		0.070983
9		2	2.9	2	2	2.9
10		0.8	0.5		3.1	1
11		0	7		1.7	1
12		3.1	1	3	4	3
13		1.7	1	4	2	1
14		8	0		2	4
15		0.3	0.2
16		4	3	5
17		2	1	6
18		2	4	7
19
Sheet1

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)

 

[Domenic]

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!

 

[Domenic]

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,""),"")

 

[WhichQuestions]

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.

[TABLE="width: 992"]
<tbody>[TR]
[TD="width: 128"][/TD]
[TD="class: xl65, width: 188"]LINEST Example: Repair

[/TD]
[TD="class: xl66, width: 228"]=LINEST(C7:H7,$C$2:$H$2^{1;2},,TRUE)[/TD]
[TD="class: xl66, width: 208"][/TD]
[TD="class: xl66, width: 240"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 948"]
<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]50000[/TD]
[TD]100000[/TD]
[TD]150000[/TD]
[TD]200000[/TD]
[TD]250000[/TD]
[TD]300000[/TD]
[TD]350000[/TD]
[TD]400000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]50k[/TD]
[TD]50-100k[/TD]
[TD]100-150k[/TD]
[TD]150-200k[/TD]
[TD]200-250k[/TD]
[TD]250-300k[/TD]
[TD]300-350k[/TD]
[TD]350-400k[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Repair[/TD]
[TD="align: right"]$1,198.54[/TD]
[TD="align: right"]$1,054.26[/TD]
[TD="align: right"]$1,352.07[/TD]
[TD="align: right"]$1,767.28[/TD]
[TD="align: right"]$1,271.92[/TD]
[TD="align: right"]$1,722.23[/TD]
[TD]TOO FEW[/TD]
[TD]TOO FEW[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]