View previous topic :: View next topic 
Author 
Message 
Theherod General User
Joined: 04 Nov 2006 Posts: 8

Posted: Sat Nov 04, 2006 1:26 am Post subject: Regression curves with the equation 


Hello,
I have succeeded to add a Regression curves to my graph, but I also need it's equation, and I couldn't find how to add it.
Does OOo support that optiion? if yes, could somone please tell me how to add it?
I tryed to find in OOo Help, and in the internet, but I didn't find anything...
Thanks a lot!
Gal. 

Back to top 


Theherod General User
Joined: 04 Nov 2006 Posts: 8

Posted: Sat Nov 04, 2006 1:32 am Post subject: 


I've forgot to say:
If I wasn't clear enough about what I'm looking for, and someone have MS Office, I mean to this option:
Right click over the graph>add a regression curves (or something like that, I don't have MS Office here)>options>show equation.
Thanks again.
Gal. 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany


Back to top 


Theherod General User
Joined: 04 Nov 2006 Posts: 8

Posted: Sat Nov 04, 2006 3:01 am Post subject: 


Actually, it's not a function. This is a Regression curve:
http://www.openofficetips.com/images/linreg2_5.jpg
http://www.openofficetips.com/images/linreg2_6.jpg
http://www.openofficetips.com/images/linreg2_7.jpg
My problem is, that I couldn't find how to make OOo to show the equation of the graph. for example:
If I make two columns, one is 1, 2, 3, 4....20, and the second one is the square of each cell of the first one, so it will be 1, 2, 9, 16....400.
If I'll make a graph of those two columns, and add a regression curve, it will look like a halfparabola. What I'm looking for, is how to make it show the equation y=x^2.
A screen shot:
http://img294.imageshack.us/img294/8997/graphea2.png
Of course that in this case, I can write it myself, but I need it to calculate something in physics.
Thanks a lot,
Gal. 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany


Back to top 


Theherod General User
Joined: 04 Nov 2006 Posts: 8

Posted: Sat Nov 04, 2006 4:29 am Post subject: 


Thanks, but II didn't mean that I want to write the equation in the graph by myself.
I had an experiment in physics, and I have the graph of the experiment. But I don't know the equation of the graph, and I need OOo to tell me what is the equation of the Regression curve of the graph.
Thanks again,
Gal. 

Back to top 


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

Posted: Sat Nov 04, 2006 6:32 am Post subject: 


I believe that this is not a function available in Calc.
I've mentioned this a few times: [url]http:\\www.padowan.dk[/url] for his excellent [and free] "Graph" program. Also mentioned is Graphmatica, but that program will give an iterated approximation to the curve, which can be unsatisfactory. There is some excellent statistical software available though, such as "Fathom", or some freeware available for checking through Google, such as available here: http://www.xray.cz/ecmcd/soft/win/index0065.html
EDIT: I had an older version of Graphmatica. The latest version gives a more accurate result for the graph equation. HINT: Click on th graph to see th equation in the equation edit box.
David. 

Back to top 


Theherod General User
Joined: 04 Nov 2006 Posts: 8

Posted: Sat Nov 04, 2006 8:17 am Post subject: 


Thanks, but as far as I see, these programs get an equation from me, and draw the function.
All I have is a few (something like ten) xy coordinates. I have to draw a regression curve, so I'll have the graph (more or less), and to get the equation of the graph. but all I can give the program is ten (more or less) xy coordinates.
Am I worng? Does those programs can do it? I would download it and check myself, but I'm working in the library now, so I can't install program, so I'll have to burn it and try to run it over my linux computer...
Anyhow, is there someone I can apply to, so this function will be added in OOo3?
Thanks a lot,
Gal. 

Back to top 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

Posted: Sat Nov 04, 2006 8:40 am Post subject: 


Theherod wrote:  Thanks, but as far as I see, these programs get an equation from me, and draw the function.
All I have is a few (something like ten) xy coordinates. I have to draw a regression curve, so I'll have the graph (more or less), and to get the equation of the graph. but all I can give the program is ten (more or less) xy coordinates.
Am I worng? Does those programs can do it? I would download it and check myself, but I'm working in the library now, so I can't install program, so I'll have to burn it and try to run it over my linux computer...
Anyhow, is there someone I can apply to, so this function will be added in OOo3?
Thanks a lot,
Gal. 
What kind of function do you want to apply to the data? Calc will apply a couple of regresion lines to the data but if you want the equation you need to calculate it using the functions in calc not in the graphics module.
If you want to add the equation to the graph then you probably need to write the equation in Draw and paste it into the graph
BTW regressions are usually referred to as lines not curves. _________________ jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 

Back to top 


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

Posted: Sat Nov 04, 2006 8:41 am Post subject: 


Theherod wrote:  Thanks, but as far as I see, these programs get an equation from me, and draw the function.
l. 
They do that. They also do pointsets and regression curves, AND will give you the equation of that curve. Unfortunately, you now mention Linux, and the programs I suggested are for Windows. Someone mentioned Gnuplot a while back, but I don't know what that will do for you.
In any event, if studying physics/statistics. It would be advantageous to know how to get the equation from the data without the use of software which is useful for greater numbers of points.
David. 

Back to top 


Theherod General User
Joined: 04 Nov 2006 Posts: 8

Posted: Sat Nov 04, 2006 9:31 am Post subject: 


Great! Thanks a lot!
Exactly what I'm looking for.
I'll check for gnuplot, but anyhow, I made a little research about these programs and linux, and I saw that with Wine, I could probably run them under linux.
I'll learn how to get the equation,but, I have to know Calculus to do that, and I'm only 10th grade. I supposed to learn it this/next year.
Again, thanks a lot!
Gal. 

Back to top 


Theherod General User
Joined: 04 Nov 2006 Posts: 8

Posted: Sat Nov 04, 2006 9:38 am Post subject: 


Quote:  What kind of function do you want to apply to the data? Calc will apply a couple of regresion lines to the data but if you want the equation you need to calculate it using the functions in calc not in the graphics module.
If you want to add the equation to the graph then you probably need to write the equation in Draw and paste it into the graph
BTW regressions are usually referred to as lines not curves. 
Sorry, I've notest your message only now.
I don't need to have the equation in the graph, actually. All I need is to have the equation.
Do you know perhaps which function will give me the equation? I thought there is no such function...
Thanks,
Gal. 

Back to top 


acknak Moderator
Joined: 13 Aug 2004 Posts: 4295 Location: ~ 40°N,75°W

Posted: Sat Nov 04, 2006 10:13 am Post subject: 


Quote:  ... Someone mentioned Gnuplot a while back, but I don't know what that will do for you. 
Gnuplot will do a very nice fit to your data, if you give it an equation to fit to.
I'm no mathematician, but I've used software to do some fitting, and I must say this discussion is making my head spin. Apologies in advance: you guys obviously understand what's going on better than I doI'm just trying to catch up.
Obviously, you have a few measured data points, but that's not enough to do anything meaningful. You must also have a model equation: a mathematical description of the physical process you have measured. There's no software on Earth that can take data points and tell you what the model equation is. You must have been told, or told to hypothesize, what equation to use.
Once you have the model equation, e.g. "y=mx+b" or "y=mx^n+b", you can use software to fit that to your data and determine a set of values for the parameters in the model equation (i.e., [m, b] or [m, b, n]) that gives the smallest difference between the values determined using the model equation and the values you measured.
And once you have the model equation and some parameters, you can overlay your data with the "fit" (model) curve (by plugging in values for x, to calculate y).
I think the chart module in OOo is limited to fitting to a linear model (i.e. a "regression") and plotting the model line. It can't label the chart with either the model equation, nor give you the parameters it determined. This is basically just a visual "trend line".
If you use formulas in the sheet, you can perform the fit to a linear or logarithmic model equation, using the LINEST and LOGEST functions. These will give you the parameters as output, but then you must use the sheet to generate the model data from the equation and plot that on your chart. If you want either the model equation or the parameters displayed on the chart, you have to do it by hand with a drawing or formula object.
If you need a different model equation, or if you need the chart to show the model equation and/or the parameters automatically, then you need to use other software. I've used gnuplot (http://www.gnuplot.info) extensively on Linux and Windows: it's not particularly friendly to use, but there are plenty of tutorials and examples around, and it is very robust for doing the actual fit. 

Back to top 


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

Posted: Sat Nov 04, 2006 10:34 am Post subject: 


Theherod wrote: 
I'll learn how to get the equation,but, I have to know Calculus to do that, and I'm only 10th grade. 
Perhaps not calculus, but a little more than is generally taught in grade 10, here at least.
Exteacher talking here:
Now you should have a means of finding the equation using a graph. However, I'd caution you very strongly about trying to run before you can walk, and, if doing a serious study beyond the average, look into the mathematics of the problem before even thinking of using software for a quick and easy solution. With so few points, why do you need a second order polynomial function? [No need to reply to that, just ask yourself.]
David. 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Sat Nov 04, 2006 3:37 pm Post subject: 


So you are not intersted in macros nor plugins.
Well, I spent some hour and found a solution, which creates a regression from
1(linear) to 4. You may add more dimensions. The formula is shown in the legend of the graph.
Use another sheet, so you can follow my instructions. Everything depends on
positions. My sheet is named "Sample".
We have two columns x and y, holding up to 100 pairs of x and ymeasurements.
A4:B4: columnlabels ("x", "y")
C4(third label for regression): ="y = "&H1&G1&F1&E1&D1
Data>Define: xyData =$Sample.$A$4:$C$105
Choose a larger range now if this are not enough rows.
We paste our values below the headers and sort by X. This is required by an
x/ygraph anyway.
Select single cell in xyData and call Data>Sort:Sort ascending by "x". Later
we just call Data>Refresh in order to repeat the same sorting with new data.
Let's implement some dynamic resizing.
Call the namesdialog(Ctrl+F3) and enter the following pairs of names and
refs. For each name, enter name and ref, hit button "Add":
aXvalues =OFFSET(xyData;1;0;COUNT(INDEX(xyData;0;1));1)
aYvalues =OFFSET(xyData;1;1;COUNT(INDEX(xyData;0;1));1)
cDim =$Sample.$A$1
exp =$Sample.$D$4:$H$4
format =$Sample.$D$2:$H$2
poly =$Sample.$D$3:$H$3
Button "OK"
Recall the namesdialog and add another one, depending on 3 of the existing ones:
aTest =OFFSET(exp;1;0;ROWS(aXvalues);cDim+1)
"Add", "OK"
Let's use the stuff:
A1(cDim): 4
C5: =SERIESSUM($A5;0;1;poly)
Copy down C5 until end of xyData.
D4:H4(exp): 0 1 2 3 4
Make shure Tools>CellContents>AutoCalculation is checked
Click D5 and enter an arrayformula (finish with Ctrl+Shift+Enter instead of Enter)
=OFFSET(xyData;1;0;ROWS(xyData)1;1)^D$4
Copy the array and paste in single steps to E5, F5, G5, H5
Arrayformula D3:H3(poly):
=TRANSPOSE(MMULT(MINVERSE(MMULT(TRANSPOSE(aTest);aTest));MMULT(TRANSPOSE(aTest);aYvalues)))
D2:H2(format): set 5 format strings to +0,000;0,000
This creates 3 decimal digits with leading +/
D1:H1(normal formulae are equivalent to array in this case):
=IF(ISNA(poly);"";TEXT(poly;format)&IF(exp=0;"";IF(exp=1;"x";"x^"&exp)&" "))
Everything depends on consecutive numeric values below "x" and "y". No gaps, no strings!
Data>Select... xyData
Create your x/yscatter from database range xyData with labels in first row and legend. The legend shows the regression formula as label of the calculated regression.
Change cell cDim (A1).
Last edited by Villeroy on Thu Sep 27, 2007 3:28 pm; edited 1 time in total 

Back to top 




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
