Category Archives: charting

Excel Super Links 1-150

This time you really hit the jackpot! >850 Excel and Power BI Links, all of the best articles. Make this the cornerstone of your Excel resource library. Download the Word document here.

Excel Super Links 1-150

 

 

Advertisements

#Excel Advanced Filter: Selectively Show Control Chart Data by Standard Deviation

 

For this technique, I am building it on the workbook made for the following article. Please download the example file and read the article, since the functionality is synergistic with it.

https://dhexcel1.wordpress.com/2017/05/17/excel-modifying-control-chart-data-to-remove-outliers-with-excel-formulas-by-david-hager/

When viewing a control chart, it is useful to be able to view only data within set control limits. For example, you would like to view all data within one sigma of the mean. The technique described here allows you to do that for 1, 2, or 3 sigma. The key to accomplish this is Excel’s advanced data filter. The advanced filter uses a boolean formula to filter a table of data, starting at the first row of the table. The formula in A2 gives the desired result.

=AND(B6<CHOOSE(SigmaKeep,D6,E6,F6),B6>CHOOSE(SigmaKeep,G6,H6,I6))

where SigmaKeep is a worksheet cell (G2) with a data validation list of 1,2,3. Cells D6 and G6 (and the corresponding columns) contain formulas that calculate the +1 and -1 sigma from the mean for the data in column B. The 2nd and 3rd sigma are for E6,H6 and F6,I6 respectively. The following figure shows the advanced filter dialog box and the input ranges required, where SigmaKeep is set at a value of 2.

 ModifyStdData_AdvFilter1

After the filter is applied, note the difference in the data in the control chart versus the original in the first figure.

 ModifyStdData_AdvFilter2

The only caveat to this technique is that the advanced filter has to be cleared (Data, Sort & Filter, Clear) before a different KeepSigma value can be applied. This can be used in conjunction with the removal of outliers as discussed in the original article.

The example file can be downloaded here.

ModifyStdData_AdvFilter