How to Calculate the Upper Control Limit with a Formula in Excel – 4 Steps

The general formula to determine the upper control limit is:

STEP 1-  Enter Sample Data to calculate the Upper Control Limit

• Data was entered in the Sample Data column.

STEP 2 – Determine the Average of Sample Data

• Use the AVERAGE function. Enter the following formula in D5.

• Drag down the Fill Handle to see the result in the rest of the cells.

STEP 3 – Find the Standard Deviation

• Use the STDEV.S function to calculate the standard deviation. Enter the following formula in C16.

STEP 4 – Calculate the Upper Control Limit with a Formula

• Enter the following formula in E5 and press Enter.

• The upper control limit is displayed in E5.
• Drag down the Fill Handle to see the result in the rest of the cells.

How to Determine LCL with UCL and Create a Chart

• Calculate the upper control limit following the previous steps.
• Enter the following formula in F5 to get the lower control limit.

• Drag down the Fill Handle to see the result in the rest of the cells.

• Select the whole dataset.

• Go to the Insert tab and select Line or Area Chart > 2-D > Line.

• The line chart is displayed. Name it.

• Select any data point in the line chart and right-click it.
• Select Add Data Label.

• Add other Data Labels.

Download Practice Workbook

Download the practice workbook here.

Related Articles

• How to Make a Control Chart in Excel

<< Go Back to Excel Control Chart | Excel Charts | Learn Excel