Ask Your Question

r squared trendline - How does it actually function?

asked 2018-06-29 17:53:13 +0200

Greetings all,

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

On 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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-06-29 21:09:11 +0200

Regina gravatar image

LibreOffice follows the ODF specification. It has the formulas in

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-06-29 17:53:13 +0200

Seen: 143 times

Last updated: Jun 29 '18