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

The general formula to determine the upper control limit is:

`UCL = Average + (3*Standard Deviation)`

### 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

`=AVERAGE(\$C\$5:\$C\$14)`

Note: A fixed cell reference was used in the formula. Press Alt + F4 to make a cell reference fixed.
• 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.
`=STDEV.S(\$C\$5:\$C\$14)`

### STEP 4 – Calculate the Upper Control Limit with a Formula

• Enter the following formula in E5 and press Enter.
`=D5+3*\$C\$16`
• 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.
`=D5-3*\$C\$16`
• Drag down the Fill Handle to see the result in the rest of the cells.

Note: In the formula for the lower control limit 3*Standard Deviation was subtracted from the Average.
• 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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF