Creating a chart with smoothed data

jones172

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 · 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 · 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 · 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 · 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 · Posted: Fri Dec 09, 2005 7:32 am Post subject:

jones172 · 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 · Posted: Sat Dec 10, 2005 9:31 am Post subject:

jones172 · 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 · Posted: Sun Dec 11, 2005 9:24 am Post subject: