30. Excel – Quantitative Control Chart Setup
In this section, the excel setup for quantitative control chart will be explained and demonstrated. But before deciding which quantitative control chart to use, please refer to the following guidelines from the Quantitative SPC explanation for selection criteria along with the equation setup for the mean line and control limits (whether it’s for X-Bar R, X-Bar s or X-MR charts).
Before initiating the Excel setup, the Excel file generated will be broken down into three sections. The Setup Section, Data Section and Graph Section for Control Charts.
*Remark: All values are stored in single cell or pre-named under name manager, so the actual formula will be depending on which cell the required elements are stored or named.
Before starting the control chart setup on graphs. The setup page and name manager’s defined name in allocated cells will have all essential parameters to reference or perform calculation of control limits.
Setup Page with required coefficient.
Defined name parameters for the quantitative control chart.
For the USL & LSL setup, please refer to the LINK from how to calculate the Cpk for optimized writing. Other characteristics such as Median, Lower Tolerance (LT) and Upper Tolerance (UT) shall be manually entered. Meanwhile, the limit coefficient will be heavily influenced by number of sample (NinS).
Control Chart Expression based on the Samples per Subgroup (NinS)
The above expression for each column can help to adjust the needed value for respective coefficient based on the amount of samples in subgroup. And by having IFS statement can certainly simplify the expression compared to the nested IF statements for conditions.
After finishing the initial setup, then the data input needs to be organized for the raw data along with the control limit calculations. Data input shall consist of the following information shown below
Data Input with Raw Data and Coefficient Lines Depending on the Chart Conditions
Once all data are entered in the data cell, the following expressions will calculate control limits for designated x bar charts, but the parameters are already pre-named in name manager before being legally used.
X Bar Control Limit and Spec Limit Representation
X Bar R and X Bar s Control Limit Expression
X-MR Control Limit Expression
Lastly, the control chart output should be similar to the chart below for both the XBar and R or s chart. The colors can be customized or arranged based on the options individual picks.
Activated X Bar s Illustration
Inactive X MR Charts
This is the quickest way to analyze the process stability or trend for quantitative characteristics such as force, temperature or any continuous process/product parameters. And also compare the differences between subgroups caused by different batches or dates.