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.

Including Sample Data for Upper Control Limit Calculation with formula in excel


STEP 2 – Determine the Average of Sample Data

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

Determining average of sample data to find upper control limit with formula

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.

Using Fill Handle to copy formula


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)

Finding Standard Deviation to calculate upper control limit with formula


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.

Calculating Upper Control Limit with Formula


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.

Determining LCL

Note: In the formula for the lower control limit 3*Standard Deviation was subtracted from the Average.
  • Select the whole dataset.

Selecting Data for chart

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

Applying option from ribbon

  • The line chart is displayed. Name it.

Primary Chart

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

Adding data label

  • Add other Data Labels.

Final chart with UCL and LCL


Download Practice Workbook

Download the practice workbook here.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo