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 4 months

Hello Abhilash,

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

Regards,

Ismet.

Featured Partner

Top Posters

Zine Eddine
2 posts
Alex Lyaschenko
27 posts
mxsiegel
0 posts
Mattvtek
0 posts
Gareth Evans
3 posts
PP Admin
3, 129 posts
Rajkamal Tangirala
5 posts
Rahul Kumar
1 posts
blake333_
0 posts
Jeamiell Ostrovsky
2 posts