The evaluation values in quality engineering(QE) are not necessarily a measured value. Please see the following materials. The evaluation values are the values actually evaluated by my trainees.
Click here for materials (Excel file) → Exaple１
If you have no experience in QE, you may not know how to see it, but I will explain it later.
A to H are the control factors. These control factors are the parameter that the yellow tube and the light blue tube are melted by heat and joined as shown in columns P to T. When you perform eighteen orthogonal array experiments, you set 2 levels for A-factor and you set 3 levels for from B to H-factors. In this case, we evaluate with the appearance. It is represented by a pattern of 1 to 5 like the P to T columns. The ideal shape is 1 point. It is jointed so that the boundary between yellow and light blue cannot be seen. Of course all around is similar to be. The jointing condition is getting to be worse as the score becomes 2, 3, and 4. In the case of 4, there are splits finely and burrs. In the case of score 5, tubes are not jointed.
As for the SN ratio, assuming that the score is y as shown in the upper right, sum of squares of y is divided by f(degree of freedom) = 1 to calculate the variance VT, and the logarithm of its reciprocal is taken and then is multiplied by 10.
The yellow cells are the points evaluated visually with the samples jointed under the conditions of 18 rows. It shows that half could not be jointed because there are some 5 points. If there is no measured value or jointing is not possible, this is called "missing value". Unexperienced people tend to narrow the area of parameter when there are missing values and adjust so that there are no missing values. But it is rather effective if there are more than half a bad value. But if the bad value is more than half, it is quite effective. According to the consultant, the truth is not understood in the case whether it is too easy(100 points) or too difficult(0 point). A problem of 50 points reflects the truth. Column M is the SN ratio calculated from these scores.
Lines 37 to 47 list the SN ratios of each control factor by level. The control factor A is nine average values, and B to H are six average values. Look at the cell formula. For example, the formula in cell B40 is "= (SUMIF (C15: C32," = 1 ", M15: M32)) / 6". The total SN ratio in the range of M15 to M32, which is "1" in the range of C15 to C32, is divided by 6. This is because there are 6 cells that are every 1, 2 and 3 . Others are the same. Please confirm that the average value of the SN ratio of each control factor level is the same in A to H, and that all the average values (cell E47) are also the same. If not, please check the formula.
In lines 49 and 50, the SN ratios are rearranged to draw a grapf of factorial effects.
Lines 54-69 is a grapf of factorial effects. Mark with a red circle where the SN ratio is high. This is the optimum condition. Green circle is the worst condition.
The 71st to 79th lines are a table for calculating the difference of value obtained by summing SN ratio of the optimum and worst conditions. Above difference is as the "estimated gain". For example, cell B74 contains the formula "= LOOKUP (B73, B38: D38, B39: D39)". In the range from B38 to D38, picks up the SN ratio of same number in cell B73.
Lines 82-84 calculate the difference between the estimated values under the optimum and worst conditions and use it as the "estimated gain". The 34th and 35th lines are the results of calculating the SN ratio by scoring the samples prepared under the optimum and worst conditions above. Lines 87 and 89 are the gains of confirmation experiments.