GNOME Bugzilla – Bug 527489
moving average enhancements
Last modified: 2008-09-24 06:09:15 UTC
In the Tools -> Statistical Analysis -> Forecast -> Moving Average function: I would like to average a series of n cells by a k interval of consecutive cells (as it is presently implemented), but to have the output as n/k cells. That is, if I have n=10 and k=2, I would like to have the output as 5 cells with the corresponding averages. I also have a question: is the moving average in Gnumeric 'prior' or 'centered'? Can one tweak it?
Could you please elaborate on your second paragraph. I sounds to me as if you are simply asking for an average rather than a moving average.
Regarding the last comment: what is the difference between "prior" and "centered"? I would think they are just a shift of each otehr, so you can just interpret the output accordingly.
'Prior' and 'centered': http://en.wikipedia.org/wiki/Moving_average About me wanting a simple average, yes, my description does sound like that. So correcting: I do want the moving average (and not a simple average), but I also need to trim the number of output cells in an orderly fashion, and due to the sheer number of cells I handle in each column (slightly over a thousand), I need it automated. I wonder if the right approach would be to use the existing Gnumeric moving average, and then another existing (?) function to copy each (say) third cell to an adjoining field. I presently make the moving average/trim in one go, with a proprietary software (which acquires data for me), before exporting it to MS Excel format and then importing it into Gnumeric. I would like to do the same transform AFTER the export, but I'll need some flexibility in Gnumeric to to so, as they are not transparent on how their moving average is done. I'll need to run some tests, so I could match the results I get in that program.
Some note: there is really no mathematical difference between centered and prior moving averages. The only difference is in the notation, which index is being used. Since the spreadsheet result does not use any indices and the location of the output is determined by the choice of the output range, it is simple a question of interpreting the results according to one or the other. If the result are placed next to the input then it will look like a "prior moving average". ----------------------------------------- Regarding: "I do want the moving average (and not a simple average), but I also need to trim the number of output cells in an orderly fashion, and due to the sheer number of cells I handle in each column (slightly over a thousand), I need it automated." Well if you have 200 data points, calculate (prior) moving averages for k = 10 and then only look at every 10th data value, then you in fact just have 20 averages of 20 groups of 10 observations. Nothing moving would be left. So this clearly would not be part of a moving averages tool, but could be achieved by using the sampling tool in connection with the moving averages tool.
I have just committed some improvements regarding the sampling tool. The sampoling tool is now "live". So when you calculate moving averages into a cell region you can use the sampling tool to set-up an extraction of every kth value. Whenever you replcae the original data a new sample is being automatically created.
I have just committed a "live" version of the moving averages tool. Together with the "live" version of the sampling tool you can easily set up a spreadsheet into which you can pour the data and get immediate output. This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.
You can now also tweak the offet (central, prior and other versions).
Cool! :D
and automatically create matching graphs (although for your data set sizes that may not be of interest)
I have added weighted and cumulative moving averages.