Thus the regresion parameters ln(a) and b are obtained. Afterwards the linearized function is fitted into transformed data using usual strategy of minimization of sum of squared residuals. Then the relation between ln(y) and x is linear. So for example to fit exponential function y=a* exp(b * x) it primarily transforms it into function ln(y)=ln(a)+b*x. It seems that in order to fit nonlinear trend into provided data, Excel primary linearize the problem. The solution of this problem was already partially mentioned in discussion here. But when one uses "add trendline" for other nonlinear function fitting (for example exponential), the resulting R^2 and regresion parameters differs from manually calculated parameters. When one uses "add trendline" for linear (and also logaritmic) function fitting, the resulting R^2 and regresion parameters are identical to manually calculated parameters. I made a same observation like it was mentioned in question.
#WHAT IS R^2 VALUE IN EXCEL TRENDLINE MANUAL#
Because I havent found too much about this on internet, so I tryed several vays of manual R^2 (coefficient of determination) evaluation in order to obtain the same results as Excel.
This question (how does the "add trendline" in Excel really works?) also puzzled me for a longer time, because in a research I need to be sure about an origin of my numbers. I obtain different values! Excel : 0.027, as I said before, and hand calculated : -0.1109 (!) and the value I obtain by hand calculating R-square from the definition (taking of course the yfit values that Excel returned me, not the one Matlab returns, as Excel and Matlab don't agree on the fitting parameters!).
the R-square value given by Excel from RSQ() function.Matlab seems to be consistent and apparently uses the strict definition of R-squared in its function. I find the same values wether I get R-squared from the fit function in Matlab or I calculate it "by hand". % mean of the yobs, total sum of squares, and residual sum of squares %% here I calculate "by hand" the R-squared, from the general definition (wikipedia!) % use the fit function in Matlab, yobs being the data
#WHAT IS R^2 VALUE IN EXCEL TRENDLINE CODE#
Here is the Matlab code I use: %% R-squared with the fit function Why Excel and Matlab don't end up with the same fitting parameters? How does Excel computes the R 2 in the 'add trendline' function, as it is obviously not the one from the definition (wikipedia)? In Matlab, using the 'fit' function, the parameters for the fitting function (and then of course the R 2) are not the ones that EXCEL has found. If I calculate it in excel using 'RSQ()' function (taking the values of the parameters Excel has found for the fitting function), or by hand using the definition (wikipedia). I plot the data, fit a power law function ('add trendline') and use the 'add trendline > options > Display R-squared value on chart' In short, I can't find the equations Excel uses for R 2.
0 kommentar(er)
