5
$\begingroup$

In a data set with thousands of data points, I am testing different short-term and longer term data outputs based on 5 rolling data points all the way to 100 rolling data points (which each value being a separate column in excel: 5, 6,..., 100).

The test I developed (with rudimentary knowledge of the whole thing) is to check which are the better "fits" of these rolling data outputs (kinda like moving averages, but not quite), in other words, if column 5, 17 or 98. In theory, the better fits should produce lower standard deviations, in the conventional calculation.

An analogy for better understanding is something like this: if I do a 5 point moving average on a five period sin wave, the output should be zero or flat, the same for a 6 point moving average on a six period sin wave, and so on all the way to 100. This is the same as saying that the standard deviation in each case should be zero.

So the test really is choosing the lowest standard deviation in each column from 5 all the way to 100, which should then provide the best "fit" (to go back to the analogy, fit with the "sin wave").

However, as the statistical inclined will quickly note (am afraid am not one of them for sure), this method presents two major difficulties:

  1. The longer data sets, ie closer to 100 rolling data points, have larger absolute deviations, thus producing higher standard deviations. So a lower standard deviation in the shorter data series may not be indicative of better "fitting", just lower absolute deviations;
  2. Each column has a different number of rolling data points, so once again the numbers and statistical fit may not be comparable between the columns.

So my question is therefore: is there a statistical measure better than the standard deviation I used above, such that it is not sensitive to absolute deviations and number of data points?

Thanks!

$\endgroup$
  • $\begingroup$ just realized I asked a long winded question when the purpose is quite simple. I just need to get a "normalized" standard deviation. Someone suggested a t-student, but this gets quite cumbersome in excel. Am sure statisticians have found a way to eliminate differences in sample sizes and amplitudes to be able to compare standard-deviation-like measures? $\endgroup$ – user2137 Nov 25 '10 at 23:02
  • 1
    $\begingroup$ I would reccomend that you consider software designed for statisics rather than calculating your own statistics in Excel or using the opaque calculations in the analysis tool pack. Also see: stats.stackexchange.com/questions/3392/… $\endgroup$ – David LeBauer Nov 26 '10 at 2:49
5
$\begingroup$

Smoothing, rolling averages, running means... are all nice ways (perhaps) to display data. But using the results of smoothed data as an input to any statistical analysis is likely to give misleading results, especially when done by novices. William Briggs emphasizes this point in his excellent blog in this article and this one.

$\endgroup$
  • 1
    $\begingroup$ Good point, and interesting links. $\endgroup$ – David LeBauer Nov 26 '10 at 19:37
3
$\begingroup$

Based on the information given, I think that you could consider AIC, a measure of likelihood that is penalized by degrees of freedom.

$\endgroup$
  • $\begingroup$ Thanks, but what is AIC? I just realized I asked a long winded question when the purpose is quite simple. I just need to get a "normalized" standard deviation. Someone suggested a t-student, but this gets quite cumbersome in excel. Am sure statisticians have found a way to eliminate differences in sample sizes and amplitudes to be able to compare standard-deviation-like measures? Thanks again $\endgroup$ – user2137 Nov 25 '10 at 23:02
  • $\begingroup$ 'what is AIC' is answered here: stats.stackexchange.com/questions/834/… $\endgroup$ – David LeBauer Nov 26 '10 at 2:28
  • $\begingroup$ this question should also be useful stats.stackexchange.com/questions/577/… $\endgroup$ – David LeBauer Nov 26 '10 at 2:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.