R squared trendline - How does it actually function?

Greetings all,

I would like to have a deeper understanding on how the r² is actually calculated.

On wikipedia, Coefficient of determination - Wikipedia, it presents a calculation routine:

  • r²=1-SSres/SStot, where SSres is the sum of (real y - adjusted y) and SStot is the sum of (real y - average real y).

By doing this, I have got a quite different different number from the one coming with the automatic trendline option. During my bachelor years I remember being there different ways to calculate the r². Is there a way for me to dig into the functions code that returns this r² value? I believe it might be possible to do, but I have never ventured myself into these dark waters.

Looking forward a concise answer.
Thanks in advance, Community.

Juliano Finck

LibreOffice follows the ODF specification. It has the formulas in OASIS Open Document Format for Office Applications (OpenDocument) Version 1.2 - Part 2: Recalculated Formula (OpenFormula) Format.

Calculating the model parameter is done by the same algorithm as used for LINEST function.

Let X denote the array of the abscissa of the data points of the series and Y the array of the associated ordinates. _n denotes an index, ^n denotes an exponent.

For case chart:regression-force-intercept=“false” the calculation can be:

For regression type:

  • exponential, model y=AEXP(Bx): LINEST(LN(Y),X,1) results B,LN(A)
  • linear, model y=Ax+B: LINEST(Y,X,1) results A,B
  • logarithmic, model y=A*LN( x )+B: LINEST(Y,LN(X),1) results A,B
  • polynomial, model y = A_0 + A_1 x + A_2 x^2 + … + A_n x^n : LINEST(Y,X,X^2,…,X^n,1) results A_n,…A_1,A_0
  • power, model y = A*x^B : LINEST(LN(Y),LN(X),1) results B,LN(A)

For case chart:regression-force-intercept=“true” and C the value of chart:regression-intercept-value the calculation can be:

For regression type:

  • exponential, model y=CEXP(BX): LINEST(LN(Y/C),X,0) results B,0
  • linear, model y=Ax+C: LINEST(Y-C,X,0) results A,0
  • polynomial, model y = C + A_1 x + A_2 x^2 + … + A_n x^n: LINEST(Y-C,X,X^2,…,X^n,0) results A_n,…,A_1,0

You might get a better understanding, if you calculate the parameters of the trend line manually in the spreadsheet, using the LINEST function as noted above for the different trend line types.

Keep in mind, that the formula for R² as given in the linked text uses the values from the parameters of function LINEST, it does not use the values from the model directly.