@neager543... For a quicker useful explanations, post some details. Ideally, attach an Excel file that demonstrates the problem.

But at the very least, post the data, the type of chart that you use, the trendline equation and R^2 that Excel displays, and the trendline equation and R^2 that you expect.

Show the values and coefficients with the precision of 15 significant digits. (Note: That might not be the same as 15 decimal places.)

-----

Re: ``the R^2 value for a trendline in excel it always ends up different [....] The equation of the trendline will usually end up different too``

One possible explanation might be the precision of the data. That is, the number of decimal places that you enter into the calculator vs Excel.

-----

Another possible explanation is: you are using a Line chart instead of an X-Y Scatter chart.

The Line chart always uses x = 1, 2, 3 etc, which might not be the actual x-values.

-----

That said, it is true that the trendline "R^2" is calculated differently in various versions of Excel, due to recent corrections.


1. In earlier versions of Excel, the trendline "R^2" calculates the square of the Pearson correlation coefficient (R^2). That is what the Excel RSQ function returns.

But in recent versions of Excel, the trendline "R^2" calculates the coefficient of determination (r^2). I call it the CoD to avoid confusion. That is what the Excel LINEST function returns in row 3 column 1 when the last parameter is TRUE.

Usually, the RSQ and CoD are about the same value. But read the LINEST help page for details about when and how they differ.


2. For the exponential trendline, for example, the exponential formula is y = b * EXP(a*x). The corresponding linear regression formula is ln(y) = ln(b) + a*x.

In earlier versions of Excel, the exponential trendline "R^2" calculates the RSQ of the linear regression formula. Thus, it is a measure of correlation between actual ln(y) and estimated ln(y).

But in recent versions of Excel, the exponential trendline "R^2" calculates the RSQ (or CoD?) of the exponential formula. Thus, it is a measure of the correlation between actual y and estimated y.

The attached image demonstrates these differences.


-----

Attached Images

exp trendline r2.jpg (239.7 KB, 18 views)