31. Excel – Attributive Control Chart Setup
In this section, the excel setup for attributive control chart will be explained and demonstrated. But before deciding which attributive control chart to use, please refer to the following guidelines from the Attributive SPC explanation for selection criteria along with the equation setup for the mean line and control limits.
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.
Excel Tab Demonstration with Setup Page
But in order to build the Excel sheet with hyperlinks directed to certain cell. Name Manager function is a recommended function to define a name with respective value, this is also an efficient way to simplify your naming algorithm if you need to refer a cell from other tabs.
The name manager’s description and routing will be listed below
Naming Manager Routing (Select Equation and toggle the Name Manager Tab)
Prior to plotting the attributive charts, data input needs to be available before. And the following tables is a quick illustration for preparing the data. The np and p chart data table will be used to explain, but the concept is the same for c and U chart as well.
Data Table Input
Before explaining the equation sections, the name manager will come in handy to define the cell values. Please refer to the following list of names in the cell.
Name Manager’s Definition and Reference Cells
Where all values should be applied for the entire worksheet. So the named parameters can be applied across.
Home button cell for the chart tab location
Home button cell for the data tab location
All cells of failed quantity from each subgroup
Home button cell for the setup tab location
All cells of failure proportion in p chart from each subgroup
All cells of failed quantity in np chart from each subgroup
Average of np counts from all subgroups.
Position cell for np chart location.
number of subgroups plotted in group.
Average of p proportion from all subgroups.
All cells of sample size in each subgroup.
The tables below are the Excel expression I used to generate the data output. And the IF condition is used to have a cleaner expression on cell when the sample size is the same or not.
Once the setup is completed, the expected outlook of control chart should have one is enabled while the other one is disabled.
Graph Section Output