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

Creating a chart with smoothed data

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
jones172
Power User
Power User


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

PostPosted: Tue Dec 06, 2005 7:38 pm    Post subject: Creating a chart with smoothed data Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
carl
Super User
Super User


Joined: 21 Apr 2003
Posts: 920
Location: Germany

PostPosted: Wed Dec 07, 2005 12:12 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
jones172
Power User
Power User


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

PostPosted: Wed Dec 07, 2005 7:25 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Wed Dec 07, 2005 8:41 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
jones172
Power User
Power User


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

PostPosted: Thu Dec 08, 2005 8:47 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
jrkrideau
Super User
Super User


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

PostPosted: Fri Dec 09, 2005 7:32 am    Post subject: Reply with quote

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
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
jones172
Power User
Power User


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

PostPosted: Fri Dec 09, 2005 2:52 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail
jrkrideau
Super User
Super User


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

PostPosted: Sat Dec 10, 2005 9:31 am    Post subject: Reply with quote

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
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
jones172
Power User
Power User


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

PostPosted: Sat Dec 10, 2005 3:55 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
jrkrideau
Super User
Super User


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

PostPosted: Sun Dec 11, 2005 9:24 am    Post subject: Reply with quote

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 Crying or Very sad 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
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
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
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