Trendline R-squared value higher when y-int fixed!?

**hot toddy** · 07-20-2023, 07:47 PM

I have an X-Y Scatter plot of some data and have added a linear trendline. When I set the intercept to zero (bottom chart pictured) I get a higher R-squared value than when I let Excel determine it (top chart pictured).

I thought that Excel optimised the intercept and slope for the highest R-squared value. Either I'm wrong about that or Excel is misbehaving.

I suspect the former (i.e. I'm wrong). If so, this become more a statistics question than an Excel question per se... What is Excel's method for determining intercept and slope of a linear trendline, and is that more justified than optimising for R-squared?

I'm using Excel 365.

Screenshot 2023-07-21 093229.png

**hot toddy** · 07-20-2023, 08:12 PM

Upon further investigation, I believe trendline intercept and slope is optimised for least RMS error, and that leads to a different solution than optimising for R-squared value (which is less surprising now that I've thought more deeply about it).

Trendline R-squared value higher when y-int fixed!?

LinkBack

Thread Tools

Rate This Thread

Display

Trendline R-squared value higher when y-int fixed!?

Re: Trendline R-squared value higher when y-int fixed!?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Higher values in Col A than Col B, sum rows with the higher value and show difference

Getting a fixed R-square value from linear trendline

[SOLVED] Excel 2010 Trendline With Data labels or number on forward forecast trendline

Adding data labels to trendline and populate cells with data from trendline (Mac)

Difference between trendline and regression R squared values

Identifying 3 cells higher & lower vales & substract higher to lower values

R-squared from a trendline

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions