Filling in the Excel Sheet for Cpk Calculation For the data provided below, fill in all the highlighted values in the Excel sheet for the Cpk problem: ne4. Final problems for ch6, 2.8 20194sx. Place the data provided below for X under the X column in the Excel sheet. Calculate X-bar, Cp, and Cpk. Interpret the indices. The specification limits are 50 +/- 0.5. Provide all the answers under the Excel sheet. The answer requested here under CANVAS is only for R-bar. The grade you will receive is not the final score for this problem. I will have to manually grade this part of the exam. X-bar 50.4 0.62 50.2 0.7 0.65 50.8 0.72 49.9 0.67 50.6 0.68 50.7 0.75 50.5 0.65 50.6 0.77 0.64 0.6 0.74 50.1 50.9
The Correct Answer and Explanation is:
Step 1: Organize the Data
We are given paired values for X (individual measurements) and R (range values). We’ll input these into two columns in Excel:
X Values:
50.4, 50.2, 50.8, 49.9, 50.6, 50.7, 50.5, 50.6, 50.1, 50.9
R Values:
0.62, 0.7, 0.65, 0.72, 0.67, 0.68, 0.75, 0.65, 0.77, 0.64, 0.6, 0.74
(Note: 12 R values, though only 10 X values are clearly given — we assume this is for 10 subgroups, and each R is associated with a subgroup.)
Step 2: Calculate X-bar (Average of X values)
Add all X values and divide by the count:X-bar=50.4+50.2+50.8+49.9+50.6+50.7+50.5+50.6+50.1+50.910X\text{-bar} = \frac{50.4 + 50.2 + 50.8 + 49.9 + 50.6 + 50.7 + 50.5 + 50.6 + 50.1 + 50.9}{10}X-bar=1050.4+50.2+50.8+49.9+50.6+50.7+50.5+50.6+50.1+50.9X-bar=505.710=50.57X\text{-bar} = \frac{505.7}{10} = 50.57X-bar=10505.7=50.57
Step 3: Calculate R-bar (Average of R values)
Add all 12 R values and divide by 12:R-bar=0.62+0.7+0.65+0.72+0.67+0.68+0.75+0.65+0.77+0.64+0.6+0.7412R\text{-bar} = \frac{0.62 + 0.7 + 0.65 + 0.72 + 0.67 + 0.68 + 0.75 + 0.65 + 0.77 + 0.64 + 0.6 + 0.74}{12}R-bar=120.62+0.7+0.65+0.72+0.67+0.68+0.75+0.65+0.77+0.64+0.6+0.74R-bar=8.1912=0.6825R\text{-bar} = \frac{8.19}{12} = 0.6825R-bar=128.19=0.6825
Step 4: Calculate Cp and Cpk
Specification Limits:
- USL = 50.5
- LSL = 49.5
- Tolerance = USL – LSL = 1.0
Estimate process standard deviation using R-bar:
For subgroup size n = 5, the d2 constant ≈ 2.326σ=R-bard2=0.68252.326≈0.2934\sigma = \frac{R\text{-bar}}{d_2} = \frac{0.6825}{2.326} \approx 0.2934σ=d2R-bar=2.3260.6825≈0.2934
Cp:
Cp=USL−LSL6σ=1.06×0.2934≈0.568Cp = \frac{USL – LSL}{6\sigma} = \frac{1.0}{6 \times 0.2934} \approx 0.568Cp=6σUSL−LSL=6×0.29341.0≈0.568
Cpk:
Cpk=min(USL−Xˉ3σ,Xˉ−LSL3σ)Cpk = \min \left( \frac{USL – \bar{X}}{3\sigma}, \frac{\bar{X} – LSL}{3\sigma} \right)Cpk=min(3σUSL−Xˉ,3σXˉ−LSL)=min(50.5−50.573×0.2934,50.57−49.53×0.2934)= \min \left( \frac{50.5 – 50.57}{3 \times 0.2934}, \frac{50.57 – 49.5}{3 \times 0.2934} \right)=min(3×0.293450.5−50.57,3×0.293450.57−49.5)=min(−0.070.8802,1.070.8802)=min(−0.0796,1.2155)=−0.0796= \min \left( \frac{-0.07}{0.8802}, \frac{1.07}{0.8802} \right) = \min(-0.0796, 1.2155) = -0.0796=min(0.8802−0.07,0.88021.07)=min(−0.0796,1.2155)=−0.0796
Interpretation:
- Cp = 0.568: The process is not capable of meeting specification limits, as Cp < 1.0.
- Cpk = -0.08: Since Cpk is negative, the process mean is outside the specification limits. The process is both not centered and inadequately controlled.
- R-bar = 0.6825: This represents the average spread of data in subgroups. A high R-bar can increase standard deviation and lower process capability.
Answer for Canvas Submission:
R-bar = 0.6825
This value is critical for calculating process standard deviation and indices like Cp and Cpk, which determine if a process is capable and in control.
