R-Squared LINEST does not match chart value

[dunlop407703]

I have a data set of x and y values, my aim is to come up with a formula which I can use to input an x value to give me the expected y value.

When I plot my data on a scatter chart, using a 3rd order polynomial trend line I get an r-squared value of 0.8402. When I use LINEST to get an r-squared value for the exact same data set I get an r-squared value of 0.7207.

Also, when I use the same x value in both the equation generated from the chart, and from LINEST I get different results. On this comparison using x=1.4, my chart formula returns y=12.1912, but my LINEST returns y=11.76757.

I'm curious to know why I am getting different results, and ultimately which is more accurate. In the past I would just have used the chart equation but I am working with a dynamic data set and I'm not aware of a way to have the chart update as I enter new data, and also to pull the regression equation out of the chart without manually typing it.

I'd really appreciate any insight or help.

 

[RickXL]

Hi,

I suspect that you are using LINEST in such a way that it is giving you the statistics for a linear fit and not a polynomial one.

Here is an example of a cubic fit:

Excel 2013
	A	B	C	D	E	F	G	H	I
1	x	y	666
2	0	486.8006				x^3	x^2	x^1	x^0
3	1	579.6145			Coeffs	0.649363	4.660717	-28.2515	518.2018
4	2	510.7612			Std Err	2.327329	31.91921	119.501	117.3971
5	3	329.2976			R^2	0.80724	129.3459	#N/A	#N/A
6	4	509.2647			F	8.375606	6	#N/A	#N/A
7	5	652.5988			Sum of Sqs	420380.8	100382.2	#N/A	#N/A
8	6	769.0935
9	7	780.566
10	8	729.2849
11	9	1206.685
Sheet1

Cell Formulas
Range	Formula
B2	=A2^3+2*A2^2+$C$1*RAND()
B3	=A3^3+2*A3^2+$C$1*RAND()
B4	=A4^3+2*A4^2+$C$1*RAND()
B5	=A5^3+2*A5^2+$C$1*RAND()
B6	=A6^3+2*A6^2+$C$1*RAND()
B7	=A7^3+2*A7^2+$C$1*RAND()
B8	=A8^3+2*A8^2+$C$1*RAND()
B9	=A9^3+2*A9^2+$C$1*RAND()
B10	=A10^3+2*A10^2+$C$1*RAND()
B11	=A11^3+2*A11^2+$C$1*RAND()
F3:I7	{=LINEST(B2:B11,A2:A11^{1,2,3},,TRUE)}
Press CTRL+SHIFT+ENTER to enter array formulas.

I have tried to label the cells but the Microsoft site has the detail: {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}
See: https://support.office.com/en-gb/article/LINEST-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d

Some details of how to use LINEST can be found here: Polynomial regression
and here: www.colby.edu/chemistry/PChem/notes/linest.pdf

Regards,

 

[pgc01]

Hi

You should get the same value.

A common error is to display just a few decimals in the coefficients of the equation displayed in the chart.

Can you post the equation displayed in the chart?

 

[dunlop407703]

Hi,

I suspect that you are using LINEST in such a way that it is giving you the statistics for a linear fit and not a polynomial one.

Some details of how to use LINEST can be found here: Polynomial regression
and here: www.colby.edu/chemistry/PChem/notes/linest.pdf

Regards,

Hi Rick,

I'm pretty sure I am using the right polynomial regression, although I don't understand them fully I admit.

For more info this is a sample of my worksheet. Apologies I don't know how to nicely put data into my comment.

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SEAM[/TD]
[TD="align: center"]COUNT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]C2[/TD]
[TD="align: center"]C3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]RSQ[/TD]
[TD="align: center"]R-SQ LINEST[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]WU[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5197.548[/TD]
[TD="align: center"]-10996.3[/TD]
[TD="align: center"]7694.011[/TD]
[TD="align: center"]-1775.21[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.720730132[/TD]
[TD="align: center"]0.720730132[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











Formulas

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]E5
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=INDEX(LINEST(OFFSET(CQDB_Filter!W$3,0,0,C5),OFFSET(CQDB_Filter!X$3,0,0,C5)^{1,2,3}),1,4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]F5[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=INDEX(LINEST(OFFSET(CQDB_Filter!W$3,0,0,C5),OFFSET(CQDB_Filter!X$3,0,0,C5)^{1,2,3}),1,3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]G5[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=INDEX(LINEST(OFFSET(CQDB_Filter!W$3,0,0,C5),OFFSET(CQDB_Filter!X$3,0,0,C5)^{1,2,3}),1,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]H5[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=INDEX(LINEST(OFFSET(CQDB_Filter!W$3,0,0,C5),OFFSET(CQDB_Filter!X$3,0,0,C5)^{1,2,3}),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]J5[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=RSQ(OFFSET(CQDB_Filter!W$3,0,0,C5),OFFSET(CQDB_Filter!X$3,0,0,C5))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]K5[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=INDEX(LINEST(OFFSET(CQDB_Filter!W$3,0,0,C5),OFFSET(CQDB_Filter!X$3,0,0,C5),TRUE,TRUE),3,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

From my chart

[TABLE="width: 500"]
<tbody>[TR]
[TD]Equation[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]y=-1775.2x³+7694x²-10996x+5197.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]R-Squared[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]R2 =0.8402[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Cheers

 

[pgc01]

Hi

To find the value of r-squared you can use the linest function, like the formula that you used in K5, but you forgot the ^{1,2,3} that you used correctly in all the other Linest() functions

in K5:

=INDEX(LINEST(OFFSET(CQDB_Filter!W$3,0,0,C5),OFFSET(CQDB_Filter!X$3,0,0,C5)^{1,2,3},TRUE,TRUE),3,1)

 

[Jon Peltier]

Any reason not to select a 5-row by 4-column range, type this into the top left cell, and press Ctrl+Shift+Enter?

LINEST(OFFSET(CQDB_Filter!W$3,0,0,C5),OFFSET(CQDB_Filter!X$3,0,0,C5)^{1,2,3})

Otherwise, I think each of your cells with the formula is calculating its own LINEST formula, making it go more slowly.

 

[dunlop407703]

Hi

To find the value of r-squared you can use the linest function, like the formula that you used in K5, but you forgot the ^{1,2,3} that you used correctly in all the other Linest() functions

in K5:

=INDEX(LINEST(OFFSET(CQDB_Filter!W$3,0,0,C5),OFFSET(CQDB_Filter!X$3,0,0,C5)^{1,2,3},TRUE,TRUE),3,1)

That fixed it, thank you!

 

[dunlop407703]

Any reason not to select a 5-row by 4-column range, type this into the top left cell, and press Ctrl+Shift+Enter?

LINEST(OFFSET(CQDB_Filter!W$3,0,0,C5),OFFSET(CQDB_Filter!X$3,0,0,C5)^{1,2,3})

Otherwise, I think each of your cells with the formula is calculating its own LINEST formula, making it go more slowly.

I'm sure there is probably unnecessary parts to my workbook but I'm, not sure if this would be a good update to make.

Where I displayed one row of data previously, in reality I am doing this activity on a table of data similar to the below but with nearly 90 rows.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]SEAM[/TD]
[TD="align: center"]COUNT[/TD]
[TD="align: center"][/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]C2[/TD]
[TD="align: center"]C3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]RSQ[/TD]
[TD="align: center"]R-SQ LINEST[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]WU[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5197.548[/TD]
[TD="align: center"]-10996.3[/TD]
[TD="align: center"]7694.011[/TD]
[TD="align: center"]-1775.21[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.720730132[/TD]
[TD="align: center"]0.720730132[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]WI[/TD]
[TD="align: center"]xx[/TD]
[TD="align: center"][/TD]
[TD="align: center"]xxxxx[/TD]
[TD="align: center"]xxxxx[/TD]
[TD="align: center"]xxxxx[/TD]
[TD="align: center"]xxxxx[/TD]
[TD="align: center"][/TD]
[TD="align: center"]xxxxx[/TD]
[TD="align: center"]xxxxx[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]LX[/TD]
[TD="align: center"]xx[/TD]
[TD="align: center"][/TD]
[TD="align: center"]xxxxx[/TD]
[TD="align: center"]xxxx[/TD]
[TD="align: center"]xxxxx[/TD]
[TD="align: center"]xxxxx[/TD]
[TD="align: center"][/TD]
[TD="align: center"]xxxxx[/TD]
[TD="align: center"]xxxxx[/TD]
[/TR]
</tbody>[/TABLE]










The data being accessed by the "OFFSET(CQDB_FILTER!W$3,0,0,C5)" part of the formula is data generated using a large number of array formulas as is and my understanding was that array formulas on large amounts of data would slow down the workbook.

If you are interested I would be more than happy to share the workbook with you to see if you have a more elegant solution.

 

[Jon Peltier]

If I had that much to do, I would probably put blocks of LINEST array formulas on a backup worksheet, and my 90 row table would link to the appropriate cells of these LINEST blocks.