[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
jlrube
Newbie

Joined: 18 Jan 2007
Posts: 1

 Posted: Thu Jan 18, 2007 3:38 pm    Post subject: Finding slope of a trend line Ok basiclly I'm taking a quantitative analsis course and often we need to find a trend line. I can insert the line but I am unable to find a way to display the equation and an r^2 value. I did find the Linest function and it spits out a bunch of numbers but I have no clue what each of these numbers is and have been unable to find the documentation
noranthon
Super User

Joined: 07 Jul 2005
Posts: 3318

 Posted: Thu Jan 18, 2007 11:40 pm    Post subject: I take it that you've looked at the Help under LINEST function. More documentation is hidden on http://documentation.openoffice.org./ and there are links, at least, on http://sc.openoffice.org/ You may find further information by searching in the forum. I believe I've seen threads on trend lines._________________ search forum by month
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732

Posted: Fri Jan 19, 2007 4:22 am    Post subject: Re: Finding slope of a trend line

 jlrube wrote: Ok basiclly I'm taking a quantitative analsis course and often we need to find a trend line. I can insert the line but I am unable to find a way to display the equation and an r^2 value. I did find the Linest function and it spits out a bunch of numbers but I have no clue what each of these numbers is and have been unable to find the documentation

It sounds like you're just doing a simple linear regression of the form y= bx + a.

What you need to do is calculate the slope (b) and intercept (a) of the data. To obtain R^2 you need to calculate the correlation co-efficient and square it.

You cannot display the equation automatically on the graph. There are a couple of ways to do it (at least). One solution is to create the graph and then add a subtitle with the equation (thanks to whoever suggested this last week( or to add a text box from draw with the equation in it and place it beside the line.

The best thing of course is not to use a spread sheet for a quantitative analysis course
_________________
jrkrideau
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Fri Jan 19, 2007 4:46 am    Post subject: I think LINEST() gives all the numbers you want, the slope, intercept and R-squared value, in an array, but you'd need to check which number apears where. Alternatively you can use the SLOPE(), INTERCEPT(), and RSQ() functions individually. As far as I've seen, you can't currently get Calc to show the equation on the chart, you need to construct it yourself in the main spreadsheet.
rfvuhbtg
Newbie

Joined: 15 May 2007
Posts: 3

 Posted: Sat Nov 17, 2007 6:03 pm    Post subject: From above: "The best thing of course is not to use a spread sheet for a quantitative analysis course" Regardless of how smart aleck this sounds, many users will encounter similar problems and think that the best thing is not to use OpenOffice instead of Microsoft Office. This is only one of many niggling little simple things that prevent me from completely dumping Windows and M\$ Office. Please please PLEASE make sure that OpenOffice 3.0 competes favorably with Microsoft's junkware even in the little details! Thank you.[/i]
veloce
General User

Joined: 12 Nov 2007
Posts: 11

Posted: Sat Nov 17, 2007 6:14 pm    Post subject: Re: Finding slope of a trend line

 jrkrideau wrote: The best thing of course is not to use a spread sheet for a quantitative analysis course

A rather antiquated view IMO. In a large organisation I worked for we found that 90% of quantitative modelling was performed using spreadsheets. Why? Because it is readily available and does an at least adequate job for 85% of models.

I think it's a good idea to teach people how to use a spreadsheet tool properly rather than use a tool that they will not be able to use in the "real world" because it is not available.
_________________
Ubuntu Gutsy, OpenOffice 2.3, Netbeans5.51
Working!
David
Super User

Joined: 24 Oct 2003
Posts: 5668

jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732

Posted: Sun Nov 18, 2007 8:29 am    Post subject: Re: Finding slope of a trend line

veloce wrote:
 jrkrideau wrote: The best thing of course is not to use a spread sheet for a quantitative analysis course

A rather antiquated view IMO. In a large organisation I worked for we found that 90% of quantitative modelling was performed using spreadsheets. Why? Because it is readily available and does an at least adequate job for 85% of models.

Here are some of the arguments for not using spread sheets for statistical analysis and quantitative modelling. The focus is on Excel mainly because of its market dominance but I believe the objections apply fairly to all spreadsheets with the proviso that gnumeric's algoritihems are significantly better than Excel's in many cases. They are probably better than those of other spreadsheets but this is a guess/

http://whitepapers.techrepublic.com.com/whitepaper.aspx?docid=258040

http://www.eusprig.org/
(Also see their interesting list of Spreadsheet mistakes - news stories at
http://www.eusprig.org/stories.htm )

Microsoft Excel for Statistics
http://www.cs.uiowa.edu/~jcryer/JSMTalk2001.pdf

Using Excel for survey analysis ( by J. Cryer)
http://www.audiencedialogue.org/excel1.html

Is Microsoft Excel an Adequate Statistics Package?
http://www.practicalstats.com/Pages/excelstats.html

Use of Excel for Statistical Analysis
http://www.agresearch.co.nz/Science/Statistics/exceluse1.htm

Should Microsoft Excel Software Be Used For
Statistical Analysis Or Graphics? ( J. Cryer)
http://gcrc.ucsd.edu/biostatistics/Excel.pdf

Statistical analysis using Microsoft Excel
http://pages.stern.nyu.edu/~jsimonof/classes/1305/pdf/excelreg.pdf

Statistical flaws in Excel
http://www.mis.coventry.ac.uk/~nhunt/pottel.pdf

NOTE X: USE OF EXCEL IN STATISTICS COURSES AND
LABORATORIES - SOME PROS AND CONS
http://www.daheiser.info/excel/notes/notex.pdf

Using Excel for Statistics :Tips and Warnings

 Quote: I think it's a good idea to teach people how to use a spreadsheet tool properly rather than use a tool that they will not be able to use in the "real world" because it is not available.

I agree with the teaching people to use spreadsheets properly especially as we are unlikely to get many to give them up when they are inappropriate. When they are the best tool for the job all too often the people using them don't have the knowledge and skills to use them properly.

When a spread sheet is not appropriate it may be not that another tool is unavailable but just that a spreadsheet is something that one knows how to use (at least a bit) and it can be twisted into working in some fashion. It is often the old story that if the only tool you have is a hammer then every problem looks like a nail.
_________________
jrkrideau
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
veloce
General User

Joined: 12 Nov 2007
Posts: 11

David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Mon Nov 19, 2007 6:05 am    Post subject: Re: Finding slope of a trend line

 jrkrideau wrote: It is often the old story that if the only tool you have is a hammer then every problem looks like a nail.

It's not just that. It's the real fact that a program is written to perform a specific task. A spreadsheet is not so much a ready-to-use program in and of itself as it is a programming language. To use it effectively then, you must be a programmer, AND work within its limitations and your own, as with any programming language. However, some programming languages are much more effective than others, and there's be nothing to stop you from choosing to use another for many tasks. The spreadsheet covers a broad spectrum, but is generally weaker in each part because of it. Good enough if that's what you want and need, just not the best, or easiest at everything.

Let's face it. People use a spreadsheet to save money, but they don't generally account for their time [and frustration] as money, and it might shock them to see how much they really spend. Me, if I have the option, I'd look for something already done, use that, and simply concentrate on the task at hand. I would buy the hammer if one is available, not try to make one, and write it off at tax time.

David.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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