Tag Archives: charting

#Excel: Removing Outliers with Excel Formulas to Modify Control Limits by David Hager

Control charts are common in all types of working environments. Although the process of making a control chart is straightforward, the collected data used in the chart sometimes has outliers. For outliers, a determination of whether they have a special cause or are part of the process variation is needed. In either case, many times the decision is made to leave the outliers in the data set. However, these data points should not be used to calculate the statistical variation. A typical example is a column of collected data (in this example defined as range). A normal calculation of the mean and the variation is shown by the following formulas.

=AVERAGE(range) ‘returns 109.6014

=STDEVP(range) ‘returns 6.42546

These values can be used to create the statistical deviations needed in the chart. However, it is more desirable to recalculate those values based on a dataset without outliers. This is accomplished by the following array formulas.

=AVERAGE(IF((range<(AVERAGE(range)+(STDEVP(range)*3)))*(range>(AVERAGE(range)-(STDEVP(range)*3)))*range=0,””,range)) ‘returns 109.4274

=STDEVP(IF((range<(AVERAGE(range)+(STDEVP(range)*3)))*(range>(AVERAGE(range)-(STDEVP(range)*3)))*range=0,””,range)) ‘returns 6.09267

The formulas needed to construct the control chart are typified by the following, which returns minus 3 deviations (in column I in the example worksheet).


where Which is the name of the cell (H2) containing a data validation dropdown with a list consisting of 1 and 2 and the two choices are the calculation for raw data and modified data respectively. So, there are different formulas in C6:I6 that are filled down to create the dataset used to make the control chart shown in the figure.


I hope that you found the techniques used here informative. But, the best part of this is that you can use your data (defined as range) to visualize your data from both a raw and modified perspective in the control chart.

Note: you can turn the calculations in your control chart data set to values if you have no further plan to add data later.

You can download the control chart example file here.