How to calculate cp in excel

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