OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

line of best fit / regression
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
bauer172uw
Guest





PostPosted: Wed Oct 08, 2003 10:42 pm    Post subject: line of best fit / regression Reply with quote

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
Moderator


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

PostPosted: Thu Oct 09, 2003 12:38 am    Post subject: Reply with quote

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 x-y
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
View user's profile Send private message
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Thu Oct 09, 2003 8:08 am    Post subject: Reply with quote

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.
_________________
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
View user's profile Send private message Visit poster's website
steveAtPT
Guest





PostPosted: Mon Oct 20, 2003 7:17 pm    Post subject: line of best fit Reply with quote

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
Moderator


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

PostPosted: Mon Oct 20, 2003 11:23 pm    Post subject: Reply with quote

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
View user's profile Send private message
steveAtPT
Guest





PostPosted: Tue Oct 21, 2003 1:07 pm    Post subject: Reply with quote

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
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Wed Oct 22, 2003 9:17 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Wed Oct 22, 2003 9:44 am    Post subject: Reply with quote

I forget,
realize a graph to see your results. Not bad ?
_________________
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
View user's profile Send private message Visit poster's website
JohnV
Administrator
Administrator


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

PostPosted: Wed Oct 22, 2003 1:55 pm    Post subject: Reply with quote

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
View user's profile Send private message
dfrench
Moderator
Moderator


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

PostPosted: Thu Oct 23, 2003 12:16 am    Post subject: Reply with quote

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
View user's profile Send private message
niels
Newbie
Newbie


Joined: 07 Apr 2005
Posts: 1
Location: Iceland

PostPosted: Thu Apr 07, 2005 2:45 am    Post subject: Reply with quote

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
View user's profile Send private message
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Thu Apr 07, 2005 10:46 am    Post subject: Reply with quote

See a litle document in
http://www.ooomacros.org/user.php#104423
Least Squares Fitting
It's an old document with a macro difficult to use because it doesn't have a dialog but perhaps a good start.
I don't know if the problem of printing the equation is resolved in OOo2.0
_________________
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
View user's profile Send private message Visit poster's website
b4codes
General User
General User


Joined: 07 May 2006
Posts: 7

PostPosted: Tue May 16, 2006 12:26 pm    Post subject: Re: line of best fit / regression Reply with quote

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
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Tue May 16, 2006 3:22 pm    Post subject: Re: line of best fit / regression Reply with quote

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
View user's profile Send private message
cmolinap
General User
General User


Joined: 04 May 2006
Posts: 16
Location: Nicaragua

PostPosted: Tue May 16, 2006 3:29 pm    Post subject: Reply with quote

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 Time-Series Library) and you can get in http://gretl.sourceforge.net/
It's free.
_________________
cmolinap
Saludando desde Masaya, Nicaragua!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group