EL MECANISMO VICTIMARIO, CLAVE DEL PROCESO DE HUMANIZACIÓN

A personal computer, either a PC or a Mac, is very frequently used with a spreadsheet to calculate the summary numbers we have been discussing. One of the spreadsheets used most frequently by engineers is Microsoft® Excel, which includes a good number of statistical functions. Excel will be used in the computer methods dis­

cussed in this book.

Using a computer can certainly reduce the labor of characterizing a large set of data. In this section we will illustrate using a computer to calculate useful summary numbers from sets of data which might come from engineering experiments or measurements. The instructions will assume the reader is already reasonably familiar with Microsoft Excel; if not, he or she should refer to a reference book on Excel; a number are available at most bookstores. Some of the main techniques useful in statistical calculations and recommended for use during the learning process are discussed briefly in Appendix B. Calculations involving formulas, functions, sorting, and summing are among the computer techniques most useful during both the learning process and subsequent applications, so they and simple techniques for producing graphs are discussed in that appendix. Furthermore, in Appendix C there is a brief listing of methods which are useful in practice for Excel once the concepts are thoroughly understood, but they should not be used during the learning process.

The Help feature on Excel is very useful and convenient. Access to it can be obtained in various ways, depending on the version of Excel which is being used.

There is usually a Help menu, and sometimes there is a Help tool (marked by an arrow and a question mark, or just a question mark).

Further discussion and examples of the use of computers in statistical calcula­

tions will be found in section 4.5, Chapter 4. Some probability functions which can be evaluated using Excel will be discussed in later chapters.

Example 3.4

The numbers given at the beginning of section 3.2 were as follows:

Group A:

Group B:

Group C:

2, 3, 4, 8 1, 2, 4, 10 0, 1, 5, 11

Find the sample variance and the sample standard deviation of each group of numbers.

Use both equation 3.8 and equation 3.11 to check that they give the same result. This example is mostly the same as Example 3.1, but now it will be done using Excel.

Answer:

Table 3.3: Excel Worksheet for Example 3.4

A B C D E

Deviations Sqd C8^2=,etc 5.0625 10.5625 18.0625

1.5625 5.0625 10.5625

0.0625 0.0625 0.5625

14.0625 33.0625 45.5625

Sum Devn Sqd Sums 20.75 48.75 74.75

The worksheet is shown in Table 3.3. The letters A, B, C, etc. across the top are the column references, and the numbers 1, 2, 3, etc. on the left-hand side are the row references. The headings for Groups A, B, and C were placed in columns C, D, and E of row 1. Names of quantities were placed in column A. Statements of formulas are given in column B. The individual entries or values were placed in cells C2:E5, that is, rows 2 to 5 of columns C to E. Cell C6 was selected, and the AutoSum tool (see section (d) of Appendix B) was used to find the sum of the entries in Group A. The sums of the entries in the other two groups were found similarly. Note that the AutoSum tool may not choose the right set of cells to be summed in cell E6. Cell C7 was selected, and the formula =C6/4 was typed into it and entered, giving the result 4.25. Then the formula in cell C7 was copied, then pasted into cell D7 (to appear as

=D6/4 because relative references were used) and entered; the same content was pasted into cell E7 as =E7/4 and entered. Again both results were 4.25.

N 2

∑ ⁽

^{xi − x}

⁾

2 i=1

According to equation 3.8 the sample variance is given by s = . N −1 Deviations from the arithmetic means were calculated in rows 8 to 11. Cell C8 was selected, and the formula =C2–$C$7 was typed into it and entered, giving the result –2.25. Notice that now, although the reference C2 is relative, the reference $C$7 is absolute. Then when the formula in cell C8 was copied, then pasted into cell C9, the formula became = C3 – $C$7; the formula was entered, giving the result –1.25.

Pasting the formula into cells C10 and C11 and entering gave the results –0.25 and (+)3.75. Similarly, the formula = D2 – $D$7 was entered in cell D8 and copied to cells D9, D10, D11 and entered in each case. A similar formula was entered in cell E8, copied separately to cells E9, E10, E11, and entered in each.

Deviations were squared in rows 13 to 16. The formula = C8^2 in cell C13 was copied to cells D13 and E13, and similar operations were carried out in cells

C14:E14, C15:E15, and C16:E16. Deviations were summed using the AutoSum tool in cells C17:E17, but we have to be careful again with the sum in cell E17. Then variances are the quantities in cells C17:E17 divided in each case by 4 – 1 = 3.

Therefore the formula C17/3 was entered in cell C18, then copied to cell D18 and modified to D17/3 before being entered, and similarly for cell E18. As the quantities in cells C18:E18 were answers to specific questions, they were put in bold type by choosing the Bold tool (marked with B) on the standard tool bar. Furthermore, they were put in a format with three decimal places by choosing the Format menu, the Number format, Number, then writing in the code 0.000 before choosing OK or Return. This gave the answers according to equation 3.8.

N 2 2

∑

^{xi − N}

^{( )}

^x

2 i=1

According to equation 3.11 the sample variance is given by s = . N −1

Squares of entries were placed in cells C20:E23 by entering =C2^2 in cell C20, copying, then pasting in cells D20 and E20, and repeating with modifications in C21:E21, C22:E22, and C23:E23. The squares of entries were summed using the AutoSum tool in cells C24, D24, and E24. Four times the squares of the arithmetic means, 4*C7^2, 4*D7^2, and 4*E7^2, were entered in cells C25, D25, and E25 respectively. These quantities were subtracted from the sums of squares of entries by entering =C24-C25 in cell C26, and corresponding quantities in cells D26 and E26.

Then values of variance according to equation 3.11 were found in cells C27, D27, and E27. These also were put in bold type and formatted for three decimal places.

Finally, standard deviations were found in cells C28, D28, and E28 by taking the square roots of the variances in cells C27, D27, and E27. As answers, these also were put in bold type and formatted for three decimals.

The results verify that equations 3.8 and 3.11 give the same results, but equation 3.11 generally involves fewer arithmetic operations.

Using Excel on a computer can save a good deal of time if the data set is large, but if as here the data set is small, hand calculations are probably quicker. Results of experimental studies often give very big data sets, so computer calculations are very often advantageous.

Example 3.5

To start a program to improve the quality of production in a factory, all the items coming off a production line, under what we have reason to believe are normal operating conditions, are examined and classified as “good” items or “defective”

items. The number of defective items in each successive group of six is counted. The results for 60 groups, 360 items, are shown in Table 3.4. Find the mean, median, mode, first quartile, third quartile, eighth decile, ninth decile, proportion defective in the sample, first estimate of probability that an item will be defective, sample vari­

ance, sample standard deviation, and coefficient of variation.

Table 3.4: Numbers of Defectives in Groups of Six Items

1 0 0 0 0 0 0 0 0 0 0 0

0 0 0 0 1 0 0 0 0 0 1 0

0 1 0 0 1 0 0 0 0 2 0 0

0 0 0 0 2 0 0 1 0 0 1 0

1 0 0 0 0 1 0 0 1 0 0 0

This is the same as Example 3.2, but now we will use Excel.

Answer: The data of Table 3.4 were entered in column A of an Excel work sheet;

extracts are shown in Table 3.5. These data were copied to column B, then sorted in ascending order as described in section (c) of Appendix B. The order numbers were

obtained in column C using the AutoFill feature with the fill handle, as also de­

scribed in that section of Appendix B. Rows 3 to 62 show part of the discrete data of Example 3.2 after sorting and numbering on Microsoft Excel.

Table 3.5: Extracts of Work Sheet for Example 3.5

A B C D E

Unsorted Sorted Order No.

1 0 1

s^2= (D70-(C70^2/B70))/(B70-1)= 0.250

s= SQRT(E73)= 0.500

Coeff. of var.= E74/E72= 214%

With the sorted data in column B of Table 3.7 and the order numbers in column C, it is easy to pick off the frequencies of various numbers of defectives. Thus, the number of groups containing zero defectives is 48, the number containing one defective is 58 – 48 = 10, and the number containing two defectives is 60 – 58 = 2.

The resulting numbers of defectives and the frequency of each were marked in cells A64:B69. The mode is the number of defectives with the largest frequency, so it is 0 in this example. Products x_i *f_i and x_i ²*f_i were found in cells C65:D69. The formulas were entered in the form for relative references in cells C67 and D67, so copying them one and two lines below gave appropriate products. Then the Autosum tool (marked Σ) on the standard toolbar was used to sum the columns for each of fi, x_if_i, and x_i ²f_i and enter the results in row 70. The sum of the calculated frequencies should check with the total number of groups, which is 60 in this case. Then from

∑

^{f xi i 14}

standard deviation, s, is found in cell E74, with a result of 0.500. The coefficient of variation is given in cell E75 as 214%. Of course, all quantities must be clearly labeled on the spreadsheet. Labels are shown in rows 1, 2, 64, 65, 70, and 72 to 75, and explanations are given in rows 66 and 72 to 75.

Problems

1. The same dimension was measured on each of six successive parts as they came off a production line. The results were 21.14 mm, 21.87 mm, 21.53 mm, 21.37 mm, 21.61 mm and 21.93 mm. Calculate the mean and median.

2. For the measurements given in problem 1 above, find the variance, standard deviation, and coefficient of variation

a) considering this set of values as a complete population, and

b) considering this set of values as a sample of all possible measurements of this dimension.

3. Four items in a sequence were measured as 50, 160, 100, and 400 mm. Find their arithmetic mean, geometric mean, and median.

4. The temperature in a chemical reactor was measured every half hour under the same conditions. The results were 78.1°C, 79.2°C, 78.9°C, 80.2°C, 78.3°C, 78.8°C, 79.4°C. Calculate the mean, median, lower quartile, and upper quartile.

5. For the temperatures of problem 4, calculate the variance, standard deviation, and coefficient of variation

a) considering this set of values as a complete population, and

b) considering this set of values as a sample of all possible measurements of the temperature under these conditions.

6. The times to perform a particular step in a production process were measured repeatedly. The times were 20.3 s, 19.2 s, 21.5 s, 20.7 s, 22.1 s, 19.9 s, 21.2 s, 20.6 s. Calculate the arithmetic mean, geometric mean, median, lower quartile, and upper quartile.

7. For the times of problem 6, calculate the variance, standard deviation, and coefficient of variation

a) considering this set of values as a complete population, and

b) considering this set of values as a sample of all possible measurements of the times for this step in the process.

8. The numbers of defective items in successive groups of fifteen items were counted as they came off a production line. The results can be summarized as follows:

No. of Defectives Frequency

0 57

1 57

2 18

3 5

4 3

>4 0

a) Calculate the mean number of defectives in a group of fifteen items.

b) Calculate the variance and standard deviation of the number of defectives in a group. Take the given data as a sample.

c) Find the median, lower quartile, upper quartile, ninth decile, and 95th percentile.

d) On the basis of these data estimate the probability that the next item pro­

duced will be defective.

9. Electrical components were examined as they came off a production line. The number of defective items in each group of eighteen components was recorded.

The results can be summarized as follows:

No. of Defectives Frequency

0 94

1 52

2 19

3 3

>3 0

a) Calculate the mean number of defectives in a group of 18 components.

b) Taking the given data as a sample, calculate the variance and standard deviation of the number of defectives in a group.

c) Find the median, lower quartile, upper quartile, and 95th percentile.

e) On the basis of these data, estimate the probability that the next component produced will be defective.

Computer Problems

Use MS Excel in solving the following problems:

C10. The numbers of defective items in successive groups of fifteen items were counted as they came off a production line. The results can be summarized as follows:

No. of Defectives Frequency

0 57

1 57

2 18

3 5

4 3

>4 0

a) Calculate the mean number of defectives in a group of fifteen items.

b) Calculate the variance and standard deviation of the number of defectives in a group. Take the given data as a sample.

c) Find the median, lower quartile, upper quartile, ninth decile, and 95th percentile.

d) On the basis of these data estimate the probability that the next item pro­

duced will be defective.

This is the same as Problem 8, but now it is to be solved using Excel.

C11. Electrical components were examined as they came off a production line. The number of defective items in each group of eighteen components was recorded. The results can be summarized as follows:

No. of Defectives Frequency

0 94

1 52

2 19

3 3

>3 0

a) Calculate the mean number of defectives in a group of 18 components.

b) Taking the given data as a sample, calculate the variance and standard deviation of the number of defectives in a group.

c) Find the median, lower quartile, upper quartile, and 95th percentile.

e) On the basis of these data, estimate the probability that the next component produced will be defective.

This is the same as Problem 9, but now it is to be solved using Excel.

C H A P T E R 4

Grouped Frequencies and

In document L A EVALUACIÓN DE LA MODERNIDAD EN LA TEORÍA MIMÉTICA DE RENÉ GIRARD. (página 58-63)