LINEST array issues

[dpofarre]

Okay, here's one I've been trying to work for some time, with LINEST.

Overview:

I have seven numbers in "A" column (x) and seven in "B" . In "C", I display a "YES" if the value in "B" is less than or equal to 10, and a "NO" if the value in "B" is greater than 10. This is to attempt to use the linest array to find the slope and y-intercept of the curve fit. The idea is to eliminate the y-values with a "NO" next to them (greater than 10), and the corresponding x-values that make up the curve, and then find slope and intercept. Here is what I have so far:

{=LINEST(IF(C1:C7="YES",B1:B7,A1:A7))}

I get some values, but they are not the slope and y-intercept of the curve. So I thought another attempt would do it.

Second idea:

I took the "NO" points, which I considered invalid for the curve, and reassigned an #N/A value to the x and y-value for that point. When I chart this data, and highlight all cells, good and bad, the chart is able to throw out the #N/A points, and still produce a curve with a correct trendline, and equation.

I thought this would work for LINEST as well. However, let's say I moved the "redone" points to "D" and "E". Then I just tried LINEST again like so:

{=LINEST(E1:E7,D1:D7,TRUE,TRUE)}

Now I get #VALUE! for the array, so long as there are any #N/A points in the curve data.

Question:

How in the world do I get Excel to take an x-y data set, thrown out the points with y-values greater than 10, and still give me the correct slope, y-intercept, and correlation coefficient with the remaining "acceptable" points? I can get the chart to understand, but convincing LINEST is quite another issue! Your help is greatly appreciated.

Dustin

 

[Aladin Akyurek]

I tried to follow the Bignum solution but I got lost in the nuts and bolts.

Book29
	A	B	C	D
1	X	Y		10
2	7	10.9		10
3	6	5.7		10
4	3	12		-0.0981
5	6.2	7.1
6	8	8.3
7	6.9	7.2
8	5.6	6.4
9	6.9	3.9
10	7.1	6.3
Sheet1

Formulas...

D1:

=MATCH(9.99999999999999E+307,A:A)

D2:

=MATCH(9.99999999999999E+307,B:B)

D3:

=MIN(D1:D2)

D4:

{=LINEST(B2:INDEX(B:B,D3),A2:INDEX(A:A,D3)^{1,2,3})}

If you are on Excel 2003, the additional calculations are not needed:

Select A1:B10.
Activate Data|List|Create List.
Check the "My list has headers" option.
Click OK.

The formula

=LINEST(B2:B10,A2:A10^{1,2,3})

will now adjust itself automatically whenever you add records to the data area.

 

[Runcer]

Worked like a charm! THank you for the help, I'm not too sure why the big number thing worked, could it be replaced with count(A:A) etc to setup the array size?

Thank you again
Joe

 

[Aladin Akyurek]

Worked like a charm! THank you for the help, I'm not too sure why the big number thing worked, could it be replaced with count(A:A) etc to setup the array size?

Thank you again
Joe

MATCH with BigNum with match-type set to 1 or omitted is faster than COUNT().