# R-Squared LINEST does not match chart value

#### dunlop407703

##### New Member
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.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### RickXL

##### MrExcel MVP
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:
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
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

##### MrExcel MVP
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

##### New Member
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.

 A B C D E F G H I J K L 1 2 3 4 SEAM COUNT b C1 C2 C3 RSQ R-SQ LINEST 5 WU 48 5197.548 -10996.3 7694.011 -1775.21 0.720730132 0.720730132

<tbody>
</tbody>

Formulas

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

<tbody>
</tbody>
F5
 =INDEX(LINEST(OFFSET(CQDB_Filter!W\$3,0,0,C5),OFFSET(CQDB_Filter!X\$3,0,0,C5)^{1,2,3}),1,3)

<tbody>
</tbody>
G5
 =INDEX(LINEST(OFFSET(CQDB_Filter!W\$3,0,0,C5),OFFSET(CQDB_Filter!X\$3,0,0,C5)^{1,2,3}),1,2)

<tbody>
</tbody>
H5
 =INDEX(LINEST(OFFSET(CQDB_Filter!W\$3,0,0,C5),OFFSET(CQDB_Filter!X\$3,0,0,C5)^{1,2,3}),1)

<tbody>
</tbody>
J5
 =RSQ(OFFSET(CQDB_Filter!W\$3,0,0,C5),OFFSET(CQDB_Filter!X\$3,0,0,C5))

<tbody>
</tbody>
K5
 =INDEX(LINEST(OFFSET(CQDB_Filter!W\$3,0,0,C5),OFFSET(CQDB_Filter!X\$3,0,0,C5),TRUE,TRUE),3,1)

<tbody>
</tbody>

<tbody>
</tbody>

From my chart

Equation
 y=-1775.2x3+7694x2-10996x+5197.5

<tbody>
</tbody>
R-Squared
 R2 =0.8402

<tbody>
</tbody>

<tbody>
</tbody>

Cheers

#### pgc01

##### MrExcel MVP

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

##### MrExcel MVP
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

##### New Member

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

##### New Member
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.

 A B C D E F G H I J K L 4 SEAM COUNT b C1 C2 C3 RSQ R-SQ LINEST 5 WU 48 5197.548 -10996.3 7694.011 -1775.21 0.720730132 0.720730132 6 WI xx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx 7 LX xx xxxxx xxxx xxxxx xxxxx xxxxx xxxxx

<tbody>
</tbody>

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

##### MrExcel MVP
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.

Replies
2
Views
658
Replies
3
Views
132
Replies
2
Views
116
Replies
0
Views
333
Replies
1
Views
186