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

Regression curves with the equation
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
The-herod
General User
General User


Joined: 04 Nov 2006
Posts: 8

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

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


Joined: 04 Nov 2006
Posts: 8

PostPosted: Sat Nov 04, 2006 1:32 am    Post subject: Reply with quote

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


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Nov 04, 2006 1:56 am    Post subject: Reply with quote

Most people here don't have Excel installed. So you should provide some information about what the function takes and what it gives.
SOmething like this?
http://www.oooforum.org/forum/viewtopic.phtml?t=23388
http://www.openofficetips.com/blog/archives/maths_statistics/index.html
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
The-herod
General User
General User


Joined: 04 Nov 2006
Posts: 8

PostPosted: Sat Nov 04, 2006 3:01 am    Post subject: Reply with quote

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 half-parabola. 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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Nov 04, 2006 4:09 am    Post subject: Reply with quote

No, this charting-module can't write equations into graphs.
Try this addon: http://www.dmaths.com/
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
The-herod
General User
General User


Joined: 04 Nov 2006
Posts: 8

PostPosted: Sat Nov 04, 2006 4:29 am    Post subject: Reply with quote

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


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat Nov 04, 2006 6:32 am    Post subject: Reply with quote

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/ecm-cd/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
View user's profile Send private message
The-herod
General User
General User


Joined: 04 Nov 2006
Posts: 8

PostPosted: Sat Nov 04, 2006 8:17 am    Post subject: Reply with quote

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


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Sat Nov 04, 2006 8:40 am    Post subject: Reply with quote

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


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat Nov 04, 2006 8:41 am    Post subject: Reply with quote

The-herod 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 point-sets 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
View user's profile Send private message
The-herod
General User
General User


Joined: 04 Nov 2006
Posts: 8

PostPosted: Sat Nov 04, 2006 9:31 am    Post subject: Reply with quote

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


Joined: 04 Nov 2006
Posts: 8

PostPosted: Sat Nov 04, 2006 9:38 am    Post subject: Reply with quote

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


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

PostPosted: Sat Nov 04, 2006 10:13 am    Post subject: Reply with quote

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


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat Nov 04, 2006 10:34 am    Post subject: Reply with quote

The-herod 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.

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


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Nov 04, 2006 3:37 pm    Post subject: Reply with quote

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 y-measurements.
A4:B4: column-labels ("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/y-graph 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 names-dialog(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 names-dialog 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>Auto-Calculation is checked
Click D5 and enter an array-formula (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
Array-formula 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/y-scatter 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
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