[Solved] Trend line calculation in chart

[epb]

Hi,

How does OpenOffice calculate the trend lines that can be inserted in graphs? According to Wikipedia there are several methods to do linear regression, and I would like to know which one OO uses... http://en.wikipedia.org/wiki/Linear_regression

Any help appreciated!

[Robert Tucker]

This page:

http://dataninja.wordpress.com/2006/02/ ... lications/

would lead me to believe that it is an ordinary least squares (OLS) fit – or was in 2006!

[Zizi64]

Self made solution:
For linear regression use the 'LINEST' function, for logarithmic regression use the 'LOGEST' function. See in OOo Help. Attention: the LINEST and LOGEST functions are ARRAY FUNCTIONS! See in OOo Help too!
If 'y' datas of chart scattered along a theoretical straight line, then the linear regression will be really "LINEAR" estimation:
y = a*x^1+b*x^0 or the known format y = a*x+b: this is the equotion of straigth line.
The LINEST function able to determine the 'a' and 'b' constant values of equotion.

The format of more-degree of polynomial expession:
The linear regression maybe write to polinomial format with 'n' exponent: y = c0*x^0 + c1*x^1 + c2*x^2 +... cn*x^n

see the example file:

Linear regression.ods

(24.7 KiB) Downloaded 511 times

Another solution:
In chart edit: Click on serial (on line) Insert->trendline...

Linear regression.png (11.9 KiB) Viewed 10002 times

[MrProgrammer]

I inserted a linear trendline on a graph and had it's equation displayed. (R squared was .9916 so the relationship was close to linear but not exact.) Then I used LINEST to calculate the slope and intercept of the least squares line. The trendline equation matches the LINEST calculation to 9 significant digits. Help says "LINEST finds a straight line y = a + bx that best fits the data, using linear regression (the least squares method)" therefore the graph's trendline is calculated the same way.

[epb]

I inserted a linear trendline on a graph and had it's equation displayed. (R squared was .9916 so the relationship was close to linear but not exact.) Then I used LINEST to calculate the slope and intercept of the least squares line. The trendline equation matches the LINEST calculation to 9 significant digits. Help says "LINEST finds a straight line y = a + bx that best fits the data, using linear regression (the least squares method)" therefore the graph's trendline is calculated the same way.

Great, that must be the answer then!

[MrProgrammer]

If we aren't sure how LINEST does its calculations we can use the actual least squares formulas for Slope

Slope.png (8.79 KiB) Viewed 9964 times

and Intercept

Intercept.png (9.09 KiB) Viewed 9964 times

In Calc terms the slope is COVAR(x;y)/VARP(x) and the intercept is AVERAGE(y)-Slope*AVERAGE(x). These results are the same as the values from LINEST, proving that it uses the least squares method.