View previous topic :: View next topic 
Author 
Message 
bauer172uw Guest

Posted: Wed Oct 08, 2003 10:42 pm Post subject: line of best fit / regression 


After I plotted my data onto a graph I would like to use OO to approximate a line of best fit (aka linear, power, logrithmic, exponential regression). Does anyone know if this is possible and if it is how can I get the equation displayed on my graph? Thanks so much for the help.
Mike
Also anyone know of any online docs /books that describe the syntax for the OO equation editor? 

Back to top 


dfrench Moderator
Joined: 03 Mar 2003 Posts: 1605 Location: Wellington, New Zealand

Posted: Thu Oct 09, 2003 12:38 am Post subject: 


Quote:  I would like to use OO to approximate a line of best fit (aka linear, power, logrithmic, exponential regression). 
1. Plot the graph as chart type xy
2. Select the plotted series with click
3. Format Object Properties; Statistics Tab ; Choose the regression type that you want
Quote:  how can I get the equation displayed on my graph 
Not so easy!
4. Use the appropriate regression function over the same data that you have plotted (eg
=LINEST() ) to calculate the factors in the equation (eg Y= m*X+c)
5. Create the equation as a string from the factors
Now you have to get the string into the chart. I have not found a simple way of doing that. You can do it in a macro though ...
6. The subtitle of the chart will be a text property ... just assign it this does the basics
Code:  sub Setsubtitle
Dim Doc As Object
Dim Charts As Object 'collection of charts
Dim Chart as Object
Doc = thiscomponent
Charts = Doc.Sheets(0).Charts
Chart = Charts(0).embeddedobject
' use the subtitle to show the equation of the fitted regression line
Chart.HasSubTitle = True
' equationstringname is the name of the cell containing the equation
Chart.Subtitle.String = doc.sheets(0).getCellRangeByName("equationstringname").string
end sub 


Back to top 


SergeM Super User
Joined: 09 Sep 2003 Posts: 3211 Location: Troyes France


Back to top 


steveAtPT Guest

Posted: Mon Oct 20, 2003 7:17 pm Post subject: line of best fit 


Linear regression is fine. I have just done a power regression line fit on my chart.
How do I get that equation?
Oo doesn't seem to have a function to calculate it although does it for the chart.
steve 

Back to top 


dfrench Moderator
Joined: 03 Mar 2003 Posts: 1605 Location: Wellington, New Zealand

Posted: Mon Oct 20, 2003 11:23 pm Post subject: 


The std built in functions are LINEST & LOGEST
LOGEST function calculates the adjustment of the entered data as a exponential regression curve (y=b*m^x).
For a Power Curve Fit (y=a*x^b) you can use the first principles approach to find a and b ... there is an example at http://www.hpmuseum.org/software/67pacs/67curve.htm 

Back to top 


steveAtPT Guest

Posted: Tue Oct 21, 2003 1:07 pm Post subject: 


You have me thinking now.
My chart plots from X(10 to 40), Y(20 to 25). Oo must fit an axis shifted curve
(y+a)=b*(x+c)^d
or a power series curve.
y=a+b*x+c*x^2+......etc.
Either way it won't come out of an Oo function.
steve 

Back to top 


SergeM Super User
Joined: 09 Sep 2003 Posts: 3211 Location: Troyes France

Posted: Wed Oct 22, 2003 9:17 am Post subject: 


For the second problem
a power series curve :
y=a+b*x+c*x^2+......etc.
there is effectively no buidin function as in MATLAB. But with LINEST in OO, this is not a difficult problem.
Suppose for instance your x data in column A
A2=1, A3=2, ... A13=12 (it's only an example)
Suppose now your y data in column B For test I use the formula : =1+2*A2+3*A2*A2+4*A2*A2*A2 and drag it to B2:B13
which is clearly of order 3 (it's not important).
and you want to determine a, b and c (order 2)
I copy column A in column E and (column A * column A) in F. If I look for order 3 I have to calculate (column A * column A* column A) in G and so on.
Then I write elsewhere :
=LINEST(B2:B13;E2:F13;1;1) with the autopilot and saying it's a matrix and obtain among others 3 values : 81 420 547 which means that the best curve of my data is
547 420x +81x^2
Conclusion : you can use LINEST with order n>1 if you precalculate x, x^2, ... x^n _________________ Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide 

Back to top 


SergeM Super User
Joined: 09 Sep 2003 Posts: 3211 Location: Troyes France


Back to top 


JohnV Administrator
Joined: 07 Mar 2003 Posts: 9183 Location: Lexinton, Kentucky, USA

Posted: Wed Oct 22, 2003 1:55 pm Post subject: 


You guys are way above my head but...
Quote:  Now you have to get the string into the chart. I have not found a simple way of doing that. You can do it in a macro though ... 
If the string is in a sheet cell then if you click that cell you can copy the string from the Input Line to the clipboard. Double click the chart so it has grey sides with black "handles", do Insert > Titles, check SubTitle and paste into the box by it.
I think this accomplishes what your after or am I missing something? 

Back to top 


dfrench Moderator
Joined: 03 Mar 2003 Posts: 1605 Location: Wellington, New Zealand

Posted: Thu Oct 23, 2003 12:16 am Post subject: 


Thanks John, the simplest ideas are the best.
I was hoping to work to the point where the charting operation carries a link to the text as in the competing but expensive product ... not there yet though. It is probably better to build a link to R 

Back to top 


niels Newbie
Joined: 07 Apr 2005 Posts: 1 Location: Iceland

Posted: Thu Apr 07, 2005 2:45 am Post subject: 


SergeM wrote:  If your questiion is how to find a linear regression you have a function for that
=pente (Data_1,Data_2) :"pente is the french name of the function and I don't know its english name : perhaps rate (m in the formula) ?
For offset c I don't find a formula. 
english: "slope", I guess. But then you get just the slope, not the whole equation. 

Back to top 


SergeM Super User
Joined: 09 Sep 2003 Posts: 3211 Location: Troyes France


Back to top 


b4codes General User
Joined: 07 May 2006 Posts: 7

Posted: Tue May 16, 2006 12:26 pm Post subject: Re: line of best fit / regression 


The function in MATLAB is "polyfit" ... it will solve your problem.
b4 : ]
bauer172uw wrote:  After I plotted my data onto a graph I would like to use OO to approximate a line of best fit (aka linear, power, logrithmic, exponential regression). Does anyone know if this is possible and if it is how can I get the equation displayed on my graph? Thanks so much for the help.
Mike
Also anyone know of any online docs /books that describe the syntax for the OO equation editor? 


Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Tue May 16, 2006 3:22 pm Post subject: Re: line of best fit / regression 


bauer172uw wrote:  After I plotted my data onto a graph I would like to use OO to approximate a line of best fit (aka linear, power, logrithmic, exponential regression). 
You might take a peek at this: http://www.padowan.dk
David. 

Back to top 


cmolinap General User
Joined: 04 May 2006 Posts: 16 Location: Nicaragua

Posted: Tue May 16, 2006 3:29 pm Post subject: 


We have
y=ax^b, so
log(y)=log(ax^b)
logy=loga + b*logx
If y*= logy, a*=loga, x*=logx then
y* = a* + bx*
In this point you can use a linear regression taking the log of the x and y variables
This eq. can be aproximate using least square method. If you need make regressions I recommend GRETL version 1.5.1 (GNU Regression, Econometrics and TimeSeries Library) and you can get in http://gretl.sourceforge.net/
It's free. _________________ cmolinap
Saludando desde Masaya, Nicaragua! 

Back to top 


