Using rolling average for data cleaning / remove outliers

 

Hi Guys,

 

I have a set of data which has daily and seasonal cycle and expands over ten years. I need to remove erroneous values using the rolling average of 5 pints at a time +/- 3 standard deviations. I might end up using more points for the rolling average (maximum of 24 values which represent the whole day) till I get something that just eliminates the wrong values. Also the +/- 3 standard deviations might be replaced by +/- 2 standard deviations as it suites my work.

 

Essentially I want to remove outliers based on a rolling average principle (averaging the whole sample would dampen the cycles I have)….

 

Can anyone suggest how to do this in Microsoft Excel?

 

Ideally, the method should show me the points it identifies as outliers before just deleting them.

Thank you,

Regards,

Abhilash

I
Ismet Kocaman 👤 Member for 16 years 3 months

Hello Abhilash,

This is a forum for MS Project. IMHO, pls consider posting your question in an Excel related forum.

Regards,

Ismet.

Forum Sponsor

Top Posters

Julian Pegg
1 posts
Peter Nagy
2 posts
Raymund de Laza
17 posts
Syed_Asad
0 posts
Tony Greyvenstein
0 posts
Ahmed Al-Jubouri
13 posts
Umar Alvi
3 posts
Sibusiso Mahlalela
0 posts
Michael Samanyayi
3 posts
Simon Gumede
0 posts