LINEST returning wrong R^2

Presto_

New Member
Joined
Apr 6, 2017
Messages
2
I have a data set with only 2 variables (x and y) and I am using LINEST to set the intercept to zero and return additional statistics. The r^2 value that LINEST returns is completely wrong though.

LINEST Returns a r^2 value of 0.999239357 where the actual r^2 value is 0.6544 that I get from a linear regression on a plot. The 0.999239357 from LINEST is far too high. I am curious as to what is causing this problem and if there is anything I can do to fix it. I would like to continue to use LINEST or some other function that I can use to set the intercept to zero.

Here is my data:

X Y
12.78874692 14.56352883
13.69998139 15.91185412
13.12604689 14.42838659
12.35962904 13.59715508
13.89994857 14.94524655
14.1493627 15.29686217
13.70240252 14.84288835
13.67470922 15.51871922
13.41659032 14.73917066
13.4249916 14.56772364
14.11832554 15.64984075
13.76440567 15.14641486
13.23559717 14.7518128
13.90397013 15.22540668
13.56784062 14.69979936
13.4847666 14.11396313
13.81941992 16.36787851
13.12653316 13.97554985
13.96703024 16.6124945
14.02102072 15.31899843
12.27260974 13.67497676
12.65017271 13.59720485
13.82095678 15.07343753
13.12702973 14.60193981
13.57999514 15.0363948
13.5986721 14.89265147
13.04855366 14.25954139
13.28885667 14.53945126
13.06484831 13.95220352
13.37722001 14.59849306
13.36289926 14.88793125
13.07861881 14.54396436
12.95479496 13.88489032
12.77414272 14.13548913
13.38982573 14.13306007
12.76126908 14.08450782
13.80294325 15.5252491
12.61220468 13.73620333
13.44249588 14.99473621
12.72451001 14.34978333
12.52160825 13.99031904
13.25404866 14.49990596
12.90542182 15.48572886
13.63808012 16.14357215
12.52340194 13.69345025
14.27277838 15.1311889
13.3028029 14.77851111
13.62390702 15.30760595
13.28649704 14.11626507
13.32095139 14.66850189
11.65027739 13.36116106
13.64113931 15.48050981
13.28871584 14.98151403
12.96553925 14.62285308
12.20640821 14.36217759
13.06031219 13.99927123
11.78520623 13.03366402
11.95662172 13.39549246
12.94724032 14.67742709
12.68657743 13.99057605
13.38506613 14.61977499
12.35808424 14.29416147
13.7598006 15.22409477
12.45876162 13.59897957
12.39006638 13.52817397
12.51215504 14.04790778
12.14879613 14.71790396
13.03299277 15.19954096
12.97365446 14.24294808
12.35391721 13.63438369
12.88874187 14.98562464
12.70735147 14.26146643
13.4381917 14.97170708
13.56330042 14.27317612
12.60234213 14.41249708
12.62493798 13.34776498
11.95017781 13.86046938
12.56148903 13.8888927
13.54023079 15.05327964
12.50775794 14.05325192
12.57702797 13.77809197
12.37775088 13.72665777
11.79779182 13.94793877
12.50655663 13.82598134
12.48044857 14.03101637
12.28169536 13.4162864
13.30223256 14.25134451
11.78596661 13.16065658
12.49533134 13.9938917
12.40985861 14.06564123
13.40169628 15.18646769
12.50185135 13.41788872
13.07150887 14.5277415
12.56439753 15.02508451
12.63405286 14.27082008
13.48691023 14.79815555
12.51411239 13.86367003
12.8386555 14.29885465
11.89763896 13.59725274
12.67220699 14.15300282
12.68724923 14.04133752
12.60049579 13.98070894
12.58358963 14.47553629
12.57349101 14.14498292
12.34404708 13.80137653
12.68561738 14.09983344
12.72784246 14.41147327
12.60939533 13.95646339
11.68677418 13.00817
13.09430043 14.38280763
12.01645329 13.61806949
12.84946802 14.46664735
12.36567085 13.76381594
12.80952758 14.38023154
12.70933674 13.7763091
12.96142016 15.10966132
11.85089342 13.12457026
12.97658773 14.14685298
13.04981744 15.08167434
12.66147856 14.08768978
12.85682311 14.23273003
13.08755626 14.50611944
12.85623746 13.97001712
12.94253984 14.59424421
13.37007598 15.03117752
13.22452416 14.94371291
13.2683558 14.69786481
13.56512759 15.66911859
12.46651495 14.02688524
12.79325816 14.35008773
12.51057778 14.12961772
13.60007399 15.2773042
12.76123753 13.85733669
13.36747956 14.89948847
13.42192545 15.1002038
13.2593878 14.95740726

Thanks for any help you can provide!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I have a data set with only 2 variables (x and y) and I am using LINEST to set the intercept to zero and return additional statistics. The r^2 value that LINEST returns is completely wrong though.

I confirmed your observation, using Excel 2007.

It is a known defect, at least through Excel 2007. See https://support.microsoft.com/en-us/help/214230/incorrect-output-is-returned-when-you-use-the-linear-regression-linest-function-in-excel

This might have been fixed in Excel 2010 or later. I know that MS made "improvement" to LINEST then.
 
Last edited:
Upvote 0
I am on Excel 2010 unfortunately. The correl function or rsq function seem to work fine but they don't allow for the intercept to be zero. Is there any other function that return the r^2 value with an intercept of zero?
 
Upvote 0
I am on Excel 2010 unfortunately. The correl function or rsq function seem to work fine but they don't allow for the intercept to be zero.

As does LINEST(ydata,xdata) without the zero intercept.

-----

Is there any other function that return the r^2 value with an intercept of zero?

One alternative is to copy-and-paste the coefficients displayed in the chart trendline label. Be sure to display the numbers with 15 significant digits of precision for best results. I use Scientific with 14 decimal places, which works for all magnitudes.

However, the trendline R^2 might only seem to be correct simply because it is different -- and in line with expectations for your particular data. (I would not expect a close correlation for that data.)

I believe I read a MS KB (support article) that indicated that trendline R^2 is also unrealiable when an intercept is specifice, at least a zero intercept. I am not bothering to do the google search this time. "The exercise is left to the student".
 
Upvote 0
Is there any other function that return the r^2 value with an intercept of zero?

Aha! I just discovered what the defect is with LINEST R2 when zero-intercept is forced. For an explanation, see the second part of my second response at https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother/excel-linest/994751d7-4f99-4f4a-ae8e-13ed895815c8 (click here).

In your example, with x-data in A2:A137 and y-data in B2:B137, you can calculate the correct R2 with the following formula:

Code:
=1 - INDEX(LINEST(B2:B137,A2:A137,FALSE,TRUE),5,2) / SUMPRODUCT((B2:B137 - AVERAGE(B2:B137))^2)

The INDEX expression returns SSres from LINEST, which I confirmed is calculated correctly. The SUMPRODUCT expression is the correct way to calculate "total sum of squares", which has a special meaning in statistics (sigh). See https://en.wikipedia.org/wiki/Total_sum_of_squares (click here).

The result is similar to the chart trendline R2 to 13 decimal places, in your example. The difference probably can be explained by binary floating-point anomalies, which are sensitive to order of calculations and if and when intermediate calculations are rounded from 80-bit to 64-bit representation in Intel-compatible CPUs. (TMI?)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top