Use LINEST with OFFSET

[mejohn]

Hello,

I have the following data set. It contains different products (P1,P2,P3,P4) and two variables (Var1, Var2).
I've condensed the product names in column F with the following formula: =OFFSET($B$3,(ROW(F1)*4)-4,0).
I've also calculated the slope and intercept for product P1 using: =LINEST(D3:D6,C3:C6)

I'd like to calculate R2 statistic into the same row in column i
Also, I'd like to use OFFSET with LINEST so that I can fill LINEST down the slope, intercept and R2 columns for use with the "Product name condensed" column.

Thanks so much in advance for your assistance.

A1	B	C	D	E	F	G	H	I
2	Product	var1	var2		Product name condensed	slope	intercept	R2
3	P1	10	0.39		P1	0.0306	0.065
4		20	0.65		P2
5		30	0.98		P3
6		40	1.30		P4
7	P2	10	0.50
8		20	0.75
9		30	1.10
10		40	1.40
11	P3	10	0.75
12		20	1.20
13		30	1.40
14		40	1.80
15	P4	10	0.30
16		20	0.70
17		30	0.90
18		40	1.20

 

[KRice]

Here is one idea...I couldn't seem to get LINEST to accept arrays formed with the OFFSET function. Additionally, the R^2 correlation coefficient isn't readily accessible on the same line as slope and intercept using LINEST. Since you're using linear regression, you might instead consider using the SLOPE, INTERCEPT, and CORREL functions and forming the arrays using INDEX/MATCH constructions like this:

Book1
	A	B	C	D	E	F	G	H	I
1
2		Product	var1	var2		Product name condensed	slope	intercept	R2
3		P1	10	0.39		P1	0.0306	0.065	0.9987
4			20	0.65		P2	0.0305	0.175	0.998124
5			30	0.98		P3	0.0335	0.45	0.990557
6			40	1.3		P4	0.029	0.05	0.991779
7		P2	10	0.5
8			20	0.75
9			30	1.1
10			40	1.4
11		P3	10	0.75
12			20	1.2
13			30	1.4
14			40	1.8
15		P4	10	0.3
16			20	0.7
17			30	0.9
18			40	1.2
Sheet3

Cell Formulas
Range	Formula
F3:F6	F3	=OFFSET($B$3,(ROW(F1)*4)-4,0)
G3:G6	G3	=SLOPE(INDEX($D$3:$D$18,MATCH($F3,$B$3:$B$18,0)):INDEX($D$3:$D$18,MATCH($F3,$B$3:$B$18,0)+3),INDEX($C$3:$C$18,MATCH($F3,$B$3:$B$18,0)):INDEX($C$3:$C$18,MATCH($F3,$B$3:$B$18,0)+3))
H3:H6	H3	=INTERCEPT(INDEX($D$3:$D$18,MATCH($F3,$B$3:$B$18,0)):INDEX($D$3:$D$18,MATCH($F3,$B$3:$B$18,0)+3),INDEX($C$3:$C$18,MATCH($F3,$B$3:$B$18,0)):INDEX($C$3:$C$18,MATCH($F3,$B$3:$B$18,0)+3))
I3:I6	I3	=CORREL(INDEX($D$3:$D$18,MATCH($F3,$B$3:$B$18,0)):INDEX($D$3:$D$18,MATCH($F3,$B$3:$B$18,0)+3),INDEX($C$3:$C$18,MATCH($F3,$B$3:$B$18,0)):INDEX($C$3:$C$18,MATCH($F3,$B$3:$B$18,0)+3))

 

[Eric W]

Here's how you can do it with LINEST and OFFSET:

Book1
	A	B	C	D	E	F	G	H	I
1
2		Product	var1	var2		Product name condensed	slope	intercept	R2
3		P1	10	0.39		P1	0.0306	0.065	0.99740094
4			20	0.65		P2	0.0305	0.175	0.99625167
5			30	0.98		P3	0.0335	0.45	0.98120219
6			40	1.3		P4	0.029	0.05	0.98362573
7		P2	10	0.5
8			20	0.75
9			30	1.1
10			40	1.4
11		P3	10	0.75
12			20	1.2
13			30	1.4
14			40	1.8
15		P4	10	0.3
16			20	0.7
17			30	0.9
18			40	1.2
19
Sheet12

Cell Formulas
Range	Formula
F3:F6	F3	=INDEX(B:B,ROW(F1)*4-1)
G3:H6	G3	=LINEST(OFFSET($D$3:$D$6,ROW(D1)*4-4,0),OFFSET($C$3:$C$6,ROW(D1)*4-4,0))
I3:I6	I3	=INDEX(LINEST(OFFSET($D$3:$D$6,ROW(D1)*4-4,0),OFFSET($C$3:$C$6,ROW(D1)*4-4,0),,1),3,1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[KRice]

Thanks, Eric. I see the problem I overlooked...I neglected to enter the LINEST/OFFSET as an array formula. I also noticed an issue with my first post...what I labeled as R2 (obtained with CORREL) is actually just R...it needs to be squared. Then my values match those you obtained with LINEST. I appreciate the tip for extracting R2 from the LINEST array output using INDEX...that's helpful.

 

[mejohn]

Thank you guys.

They both work great, although as KRice mentioned R needs to be squared.

I appreciate the help.