R-Squared LINEST does not match chart value

dunlop407703

New Member
Joined
Oct 8, 2014
Messages
24
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.
 

Some videos you may like

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
Joined
Sep 9, 2013
Messages
4,316
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>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;background-color: #BDD7EE;;">x</td><td style="font-weight: bold;text-align: center;background-color: #BDD7EE;;">y</td><td style="text-align: right;;">666</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">486.8006</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">x^3</td><td style=";">x^2</td><td style=";">x^1</td><td style=";">x^0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">579.6145</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Coeffs</td><td style="text-align: right;;">0.649363</td><td style="text-align: right;;">4.660717</td><td style="text-align: right;;">-28.2515</td><td style="text-align: right;;">518.2018</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">510.7612</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Std Err</td><td style="text-align: right;;">2.327329</td><td style="text-align: right;;">31.91921</td><td style="text-align: right;;">119.501</td><td style="text-align: right;;">117.3971</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">329.2976</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">R^2</td><td style="text-align: right;;">0.80724</td><td style="text-align: right;;">129.3459</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">509.2647</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">F</td><td style="text-align: right;;">8.375606</td><td style="text-align: right;;">6</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">652.5988</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Sum  of Sqs</td><td style="text-align: right;;">420380.8</td><td style="text-align: right;;">100382.2</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">6</td><td style="text-align: center;;">769.0935</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">7</td><td style="text-align: center;;">780.566</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">8</td><td style="text-align: center;;">729.2849</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">9</td><td style="text-align: center;;">1206.685</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=A2^3+2*A2^2+$C$1*RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=A3^3+2*A3^2+$C$1*RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=A4^3+2*A4^2+$C$1*RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B5</th><td style="text-align:left">=A5^3+2*A5^2+$C$1*RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B6</th><td style="text-align:left">=A6^3+2*A6^2+$C$1*RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B7</th><td style="text-align:left">=A7^3+2*A7^2+$C$1*RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8</th><td style="text-align:left">=A8^3+2*A8^2+$C$1*RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=A9^3+2*A9^2+$C$1*RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=A10^3+2*A10^2+$C$1*RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B11</th><td style="text-align:left">=A11^3+2*A11^2+$C$1*RAND(<font color="Blue"></font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F3:I7</th><td style="text-align:left">{=LINEST(<font color="Blue">B2:B11,A2:A11^{1,2,3},,TRUE</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<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
Joined
Apr 25, 2006
Messages
19,873
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
Joined
Oct 8, 2014
Messages
24
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.

ABCDEFGHIJKL
1
2
3
4SEAMCOUNTbC1C2C3RSQR-SQ LINEST
5WU485197.548-10996.37694.011-1775.210.7207301320.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
Joined
Apr 25, 2006
Messages
19,873

ADVERTISEMENT

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
Joined
May 14, 2003
Messages
4,933
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Oct 8, 2014
Messages
24

ADVERTISEMENT

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
Joined
Oct 8, 2014
Messages
24
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.

ABCDEFGHIJKL
4SEAMCOUNTbC1C2C3RSQR-SQ LINEST
5WU485197.548-10996.37694.011-1775.210.7207301320.720730132
6WIxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
7LXxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

<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
Joined
May 14, 2003
Messages
4,933
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,848
Messages
5,574,638
Members
412,607
Latest member
caner
Top