How to calculate cpk in excel

Calculate Cp And Cpk With Excel, "Quality Engineering"

This is case study to calculate Cp and Cpk It's for an intro to quality engineering class

Please Read it very carefully.

In the Excel file there is the case and the numbers plus questions about the process. There are several parts to it. Along with the white boards it contains all the information you should need to answer it well, It is an Excel file and you can use Excel's capabilities to make calculations easier, although it is important that I can see your logic and approach as well. Hand drawn control charts are fine, and ensure the key values asked for are written. There is a partial credit even if there are math errors provided that the approach is good.

Note: that there is more than one way to calculate Cp and Cpk and they may give slightly different numbers - either method is OK but show your working clearly.

if you hand draw anything (which you will unless you are really good at Excel) you will need to scan it of course there is a lot of app on any phone nowadays

Sheet1
ENGR 3680 Introduction to Quality Engineering
Case Study #1 Control Charts and Process Capability Return to B.North on or before October 13th 2016
NAME (PRINTED): Signature:
You are the Quality Engineer responsible for a line making electrical resistors.
As a part of your responsibilities you are setting up a control chart for the production line.
You start with 10 sets of resistance data, each with a subgroup size of 5
Set # 1 2 3 4 5 6 7 8 9 10
Resistance (ohms) 56 49 50 45 44 62 45 41 60 49
48 52 50 49 48 48 57 57 47 46
45 48 46 60 59 53 53 48 56 56
61 61 57 54 53 59 46 52 46 46
55 63 61 55 48 42 59 50 51 50
From these data, create an Average and Range Control Chart. Plot it, and label the Averages and the Upper and Lower Control Limits for both parts of the chart.
Clearly label the calculated values for the Averages and the Upper and Lower Control Limits of both parts of the chart.
The specification range for these resistors is from 38 to 62 ohms, with a target of 50 ohms.
What is the Process Potential Cp based on these data? Show how you calculated it!
What is the Process Performance Cpk based on these data? Show how you calculated it!
As the Quality Engineer responsible for this line, would you recommend 100% testing? Explain your answer in less than 25 words.
Soon after setting up these control charts, you get a sequence of mean data thus:
Resistance (ohms) 53 47 49 52 55 56 58 59 60 58
Describe your approach, and explain your reason(s), after getting these data: Use less than 25 words.
Supporting Information
Constants for X bar and R chart
n (subgroup size) A2 D3 D4 d2
2 1.880 0.000 3.268 1.128
3 1.023 0.000 2.574 1.693
4 0.729 0.000 2.282 2.059
5 0.577 0.000 2.114 2.326
6 0.483 0.000 2.004 2.534
7 0.419 0.076 1.924 2.704
8 0.373 0.136 1.864 2.847
9 0.337 0.184 1.816 2.970
10 0.308 0.223 1.777 3.078
Estimate of standard deviation s = Rbar/d2