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

Author Message
jones172
Power User

Joined: 02 Oct 2004
Posts: 77
Location: Silver Spring, Maryland, USA

 Posted: Tue Dec 06, 2005 7:38 pm    Post subject: Creating a chart with smoothed data In Calc, I am trying to create a chart, based on some 120 data points. One problem is that there is a substantial "noise" or "randomness" in the data. Upon selecting the data, in Calc, then Insert > Sheet2 > Lines I see a line chart (a preview?) which has been quite nicely smoothed in such a way as to indicate trends. However, when I actually create the chart, the smoothing has been removed, and the line bounces all over the place. (Assuming that the "smoothing" is really smoothing, rather than some sort of histogramming.) Any ideas? Tom
carl
Super User

Joined: 21 Apr 2003
Posts: 920
Location: Germany

 Posted: Wed Dec 07, 2005 12:12 am    Post subject: perhaps you should use a column of "smoothed " data. Some sort of average. eg Col B contains raw data. Col C the "smoothed" C2 =(B1:B3)/3_________________carl Using OpenOffice.org 2 on XP sp2
jones172
Power User

Joined: 02 Oct 2004
Posts: 77
Location: Silver Spring, Maryland, USA

 Posted: Wed Dec 07, 2005 7:25 pm    Post subject: Yes, I am doing that. The technical term is "moving average.' http://en.wikipedia.org/wiki/Moving_average However, the moving average isn't very smooth, and five will get you ten that Calc has a better way of doing it.
Dale
Super User

Joined: 21 Feb 2005
Posts: 1440
Location: Australia

 Posted: Wed Dec 07, 2005 8:41 pm    Post subject: Jones, Instructions for 1.1.3 (2.0 won't have changed that much - I hope):Select your data. Insert > Chart (displays the Autoformat Chart dialog) Select a Chart Type of EITHER Lines OR XY Chart Click Next For the chart variant choose one of the options with the smoothed lines (click on the picture/icon) (there's a couple of cubic spline options and a B spline option - no I don't know what that means). Use the scroll bar at the right of the variant box if the smoothed options are not visible. I tested this on random data for Line Charts and XY Charts using the Cubic Spline variant and it seems to works OK in 1.1.3 From your original post it seems that you did more or less the same but ended up with standard lines. Is this the case? What version of OO.o are you using? PS - The smoothed lines generated do not indicate trends. They still join all the dots, but there is now a radius where they change direction - not a vertex._________________Dale To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me! OOo documentation from the source http://documentation.openoffice.org Guides, FAQ, How Tos
jones172
Power User

Joined: 02 Oct 2004
Posts: 77
Location: Silver Spring, Maryland, USA

 Posted: Thu Dec 08, 2005 8:47 pm    Post subject: The smoothing is said to be doable in Excel. http://exceltips.vitalnews.com/Pages/T0972_Smoothing_Out_Data_Series.html However, I don't have good access to a copy of Excel and haven't figured out how to do it in OOo 1.1.5. Maybe it is time to look at OOo 2.0. Tom
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732

Posted: Fri Dec 09, 2005 7:32 am    Post subject:

 jones172 wrote: The smoothing is said to be doable in Excel. http://exceltips.vitalnews.com/Pages/T0972_Smoothing_Out_Data_Series.html However, I don't have good access to a copy of Excel and haven't figured out how to do it in OOo 1.1.5. Maybe it is time to look at OOo 2.0. Tom

I had a quick look at the Excel smoothing and it offers one kind of smoothing with no explaination of what it is. Calc v2. seems to offer two types and even names them so you are probably as well or better off with Calc in this case.
_________________
jrkrideau
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
jones172
Power User

Joined: 02 Oct 2004
Posts: 77
Location: Silver Spring, Maryland, USA

 Posted: Fri Dec 09, 2005 2:52 pm    Post subject: What Calc offers (in OOo 1.1.5) are what are called spline functions. http://en.wikipedia.org/wiki/Cubic_spline At the end of the day, spline functions involve an interpolation; I can't tell one way or the other if there is substantial loss of data.
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732

Posted: Sat Dec 10, 2005 9:31 am    Post subject:

 jones172 wrote: What Calc offers (in OOo 1.1.5) are what are called spline functions. http://en.wikipedia.org/wiki/Cubic_spline At the end of the day, spline functions involve an interpolation; I can't tell one way or the other if there is substantial loss of data.

Well the only smoothing I am really aware of is Lowess or Tukey's hand calculated smoothing and both seem to involve some loss of data as well. What smoothing functions don't?
_________________
jrkrideau
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
jones172
Power User

Joined: 02 Oct 2004
Posts: 77
Location: Silver Spring, Maryland, USA

 Posted: Sat Dec 10, 2005 3:55 pm    Post subject: There is such a thing as statistical filtering, using a moving average or a related technique: http://en.wikipedia.org/wiki/Special:Search?search=moving+average&fulltext=Search However, this would be clunky indeed to implement in Calc. Tom
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732

Posted: Sun Dec 11, 2005 9:24 am    Post subject:

 jones172 wrote: There is such a thing as statistical filtering, using a moving average or a related technique: http://en.wikipedia.org/wiki/Special:Search?search=moving+average&fulltext=Search However, this would be clunky indeed to implement in Calc. Tom

I no longer have the math to handle that without hours of work Looks a bit like Lowess IIRC.

I think you probably need a better graphing package. Maybe one of the stats packages? The only Open Source one that I can think of right now that will do it is R . Most of the commerical ones will I believe.
_________________
jrkrideau
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
 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