29. Excel – Process Capability Setup

This particular section is to apply when and how to calculate process capability (Cpu, Cpl or Cpk) based on the provided specification. And each formula will be illustrated with certain excel function for optimizing the view and operate more efficiently within the tabs.

*Remark: All values are stored in single cell, so the actual formula will be depending on where the required elements are stored.

First, please refer to the right hand side’s table. This is the generic layout for placing the specification including specification median, upper and lower tolerance.

Start typing your specification’s median (center value), your upper and lower tolerance (UT and LT for short) (signs included) into the respective cells.

Process Capability Specification Layout

In order to make sure the excel returns a clean format, the IF equation will be commonly used, and the IF equation will have the following format

= IF (1st Statement, 2nd Statement, 3rd Statement)


    • 1st Statement = Tested Condition to match desired requirement such as greater or less than a certain value.
    • 2nd Statement = Return Value or expression if the tested condition in the 1st statement passed.
    • 3rd Statement = Return Value or expression if the tested condition in 1st statement failed.

The following table will explain what type of expression was required for USL & LSL. (where “” = blank value)


Cpk Data Summary

In general, the minimum quantity of a single population shall be at least 30 for representation due to the central limit theorem’s approximation of the sample size is close to a normal distribution.

After completing the sample data collection (all measurements will be used as data for representation). Otherwise the expression for range cell should be similar to the expression like Cell 1:Cell 30.

The following tables will be the final expression for the Data summary indicators.

In conclusion, the sheet’s target is to help one component’s process capability with respect to different dimension can be displayed. It can also help the manufacturers to identify potential issues based on specific deficiency for the production process

Share your thoughts