LINEST function and Blank Spaces

[JeffGrant]

Hi All, I have a curly one.

over the same data set, in rows, I am running from linear regression to 5th degree regression.

As a measure of confidence level, I would like to run R^2 over each data set to determine which polynomial gives me the highest confidence level.

The issue is that the data set can have between two and ten values. So immediately, using LINEST on a fixed range will fail because of the blanks.

However, the blanks aren't really blanks, they are the results of an IFERROR statement.

I have tried using the FILTER function on the known x's and that works fine, however FILTER on the known y's - not so much.

The number of "blanks" in the known y's will always be the same in the known x's.

Can somebody please give me some direction on how best to achieve this goal?

I have changed the horizontal ranges manually in this sample data, but when I use a full data sheet, that could easy be 2000 lines, so doing it manually is a little of out of the realm of possibility.

Thanks in advance.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Form Weights (kg)										Speed (m/s)
2	10	9	8	7	6	5	4	3	2	1	10	9	8	7	6	5	4	3	2	1	Rsqd ^1	Rsqd ^ 2	Rsqd ^ 3	Rsqd^4	Rsqd^5
3	0.0	0.0	0.0	57.0	56.0	58.0	60.0	55.0	55.0	60.0				16.87	17.21	16.60	16.24	16.96	16.76	16.77	0.436215	0.473015	0.691188	0.710978	0.710978
4	0.0	57.0	56.0	56.5	57.0	57.0	55.5	58.0	58.0	58.5		16.17	16.28	16.81	17.19	16.69	16.90	16.82	16.85	17.24	0.153974	0.278945	0.279727	0.398721	0.397614
5	58.5	60.5	57.5	58.5	55.5	57.5	57.0	59.5	56.5	59.0	16.50	16.67	16.96	16.50	16.48	16.98	16.54	16.63	16.57	16.71	0.010512	0.105084	0.235134	0.282031	0.278945
6	0.0	0.0	0.0	0.0	54.0	56.0	53.0	57.5	55.5	59.0					17.00	16.75	16.80	16.68	17.28	17.08	0.00341	0.003432	0.58676	0.628552	0.627001
7	54.5	56.0	56.5	56.5	57.0	56.5	57.0	56.0	60.0	58.0	16.84	16.94	16.81	16.50	17.07	17.03	16.51	16.56	16.29	17.12	0.109844	0.227185	0.445786	0.483165	0.483307
8	0.0	0.0	0.0	0.0	0.0	57.5	57.5	58.0	58.0	58.0						16.57	16.75	16.92	16.85	17.51	0.447963	0.447963	0.447963	0.447963	0.447963
Sheet1

Cell Formulas
Range	Formula
U3:U8	U3	=RSQ(K3:T3,A3:J3)
V3	V3	=INDEX(LINEST(N3:T3,D3:J3^{1;2},TRUE,TRUE),3,1)
W3	W3	=INDEX(LINEST(N3:T3,D3:J3^{1;2;3},TRUE,TRUE),3,1)
X3	X3	=INDEX(LINEST(N3:T3,D3:J3^{1;2;3;4},TRUE,TRUE),3,1)
Y3	Y3	=INDEX(LINEST(N3:T3,D3:J3^{1;2;3;4;5},TRUE,TRUE),3,1)
V4	V4	=INDEX(LINEST(L4:T4,B4:J4^{1;2},TRUE,TRUE),3,1)
W4	W4	=INDEX(LINEST(L4:T4,B4:J4^{1;2;3},TRUE,TRUE),3,1)
X4	X4	=INDEX(LINEST(L4:T4,B4:J4^{1;2;3;4},TRUE,TRUE),3,1)
Y4	Y4	=INDEX(LINEST(L4:T4,B4:J4^{1;2;3;4;5},TRUE,TRUE),3,1)
V5,V7	V5	=INDEX(LINEST(K5:T5,A5:J5^{1;2},TRUE,TRUE),3,1)
W5,W7	W5	=INDEX(LINEST(K5:T5,A5:J5^{1;2;3},TRUE,TRUE),3,1)
X5,X7	X5	=INDEX(LINEST(K5:T5,A5:J5^{1;2;3;4},TRUE,TRUE),3,1)
Y5,Y7	Y5	=INDEX(LINEST(K5:T5,A5:J5^{1;2;3;4;5},TRUE,TRUE),3,1)
V6	V6	=INDEX(LINEST(O6:T6,E6:J6^{1;2},TRUE,TRUE),3,1)
W6	W6	=INDEX(LINEST(O6:T6,E6:J6^{1;2;3},TRUE,TRUE),3,1)
X6	X6	=INDEX(LINEST(O6:T6,E6:J6^{1;2;3;4},TRUE,TRUE),3,1)
Y6	Y6	=INDEX(LINEST(O6:T6,E6:J6^{1;2;3;4;5},TRUE,TRUE),3,1)
V8	V8	=INDEX(LINEST(P8:T8,F8:J8^{1;2},TRUE,TRUE),3,1)
W8	W8	=INDEX(LINEST(P8:T8,F8:J8^{1;2;3},TRUE,TRUE),3,1)
X8	X8	=INDEX(LINEST(P8:T8,F8:J8^{1;2;3;4},TRUE,TRUE),3,1)
Y8	Y8	=INDEX(LINEST(P8:T8,F8:J8^{1;2;3;4;5},TRUE,TRUE),3,1)

 

[JeffGrant]

UPDATE:

I found this great VBA function at Ignore zeros (or blanks) in Excel LINEST function with multiple independent variables, written be Palisek.

Works great.

I consider this thread solved now, unless somebody has a non-vba way to solve this.

 

[Domenic]

Here's a non-vba approach...

LINEST IGNORE BLANKS - Polynomial translation

Hope this helps!