You are here: HomeExcelTutorials

# Using rolling average for data cleaning / remove outliers

## Using rolling average for data cleaning / remove outliers

Price: FREE

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)....
Ideally, the method should show me the points it identifies as outliers before just deleting them.

Visit publisher's web-site: Using rolling average for data cleaning / remove outliers

Category and Tags: ExcelTutorials :

## Related Tutorials

### Averaging while removing outliers

Free: Yes
Tags: Excel ›

I am looking for a way to average my data, while removing any outliers present. Currently the raw data has time in columns and 16 data points for each time across in a row. I would like to average those 16 data points, but also remove any outliers (+/- 2 standard deviations or ><1.5xIQR is fine).

### Searching a range of values for outliers

Free: Yes
Tags: Excel ›

I've been trying with no luck to come up with a way to:
a) find all the outliers in a range of a 100 rows. The criteria is simply +/- average*2. The outliers can either be above or below.
b) colour the background of these outliers.
c) repeat a&b in the next column.
So in essence, I'm trying to find all values in a table that are twice (positive or negative) that of the average and highlight them.
Example:
A, B
In this case the outliers for A is 12 and the outliers for B is 4.
Any help, be it in a formula for a given cell, or VBA, is deeply appreciated.

### Rolling average

Free: Yes
Tags: Excel ›

I'm trying to calculate a 3 month average for a set of data but when I type in =average(d2:d4) and drag it down, the next box displays d3:d5 when I need it to take the average of d5:d7. Is there a way I could tweak the formula to do this for me?

### Rolling average

Free: Yes
Tags: Excel ›

I have been assigned a new task, to make a score card for Key Performance Indicators. I have asked the concerned departments for the data.
Now what is this term rolling average/
My data is as follows
Data : safety valve calibration
Month jan feb march apr
Planned 10 14 15 10
calibrated 5 15
How do I get a rolling average? this data is just collected from Jan 2010

### Finding Outliers and Obtaining the Data

Free: Yes
Tags: Excel ›

I need your assistance working on a large data set that I am working with. I am looking for a restrictive way of identifying the outliers in my data. I am currently using the median and an array formula to identify outliers that are two deviations from the Median Absolute Deviation. This gives me both positive and negative outliers. I used an array formula to distinguish between normal data and an outliers. However outliers shows up for both positive data and negative data. How do I distinguish between the two types of outliers?
Secondly, is there a better approach to finding outliers and how do I go about capturing the data.

## Related Applications & Scripts

### Create Rolling Text Effects

Free: No

Nuvu TextRoll - Create a Rolling Text Effect on any HTML text element. Yet Another jQuery Plugin I wrote, that makes 7 this month. Let me know what you guys think of it. Features: Set the starting font size. Set the grow to size. This will be the max font-size when the letter is animated. Use third party easing(jQuery UI, etc) or simple jQuery easing (swing, linear) Set the speed in which the font-size grows. Set the speed in which the fon-size returns to its original size. Whether to include multi letter animation. Increase the font-size on the next two and previous two adjacent letters when hovering over a letter. Creates more of a rolling effect. Set this option to allow three different color changes, on hover. Works will different typefaces(fonts)

### Travelers Inn : nice holiday / travel solution web template

Free: No
Tags: Flash › Excel Web Layouts

All contents are XML driven - All thumbnail images are externally loaded jpg's - You have a nice rolling/ swapping data & images when you change from one link to the other, notice that the current data is swapped and the new data is loaded from the previous only. - A fully functional preloader is attached with this site, which calculates how many bytes to preload and how much time is required to get the data preloaded. - A nice series of rolling buttons, just roll over & out & enjoy. - Graphics & scripts are fully customizable. - Centered in browser.

### Flash Magnifying Glass

Free: No
Tags: Flash › Excel Image Effects

This is a magnifying glass tool which zooms the part of the image you are rolling over.
Just need to create an instance of the object class Magnifying Glass with the paths of the two images as parameters.
In Version 1.1 is added custom event handler for removing the Magnifying glass on rolling out of the image. This allows you use the component as a part of bigger flash movie which contains other elements too.

### Magnifying Glass

Free: No
Tags: Flash › Excel Effects

This is a magnifying glass tool which zooms the part of the image you are rolling over. Just need to create an instance of the object class Magnifying Glass with the path to the images.
Added custom event handler for removing the Magnifying glass on rolling out of the image. This allows you use the component as a part of bigger flash movie which contains other elements too.
It is optimized and use only one image. This makes the work with the component more easier and allow the movie clip to be loaded faster.

### Acuity CMS

Free: No
Tags: ASP › Excel CMS PHP

A powerful, simple, extremely easy to use, cost effective and easy to deply content management system (CMS).
Acuity CMS comes in two flavours - ASP and PHP. This wide support for web server platforms allows you to use a highly cost effective, powerful CMS no matter where your website is hosted.
HTML tag cleaning feature helps content authors assure that web page content remains both easy to read and accessible in all web browsers. Acuity CMS offers special tools for cleaning of code created by MS Word and other external programs.