| 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 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 |
|
|
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: 8979
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 Time-Series Library) and you can get in http://gretl.sourceforge.net/
It's free.
_________________
cmolinap
Saludando desde Masaya, Nicaragua! |
|
| Back to top |
|
|
|
