# Using Excel to Solve Statistics Questions

See the Excel file for data.

(Q1) In 1970, a lottery was used to determine who would be drafted into the US Army. The 366 dates in the year were placed in individual capsules. First the 31 January capsules were placed in a box: then the 29 February capsules were added and the two months were mixed. Then the 31 March capsules were added and the three months were mixed. This process continued until all months were included. The first capsule selected was September 14, so men born on that date were drafted first. The accompanying list show the 366 dates in the order of selection.

a- Use the runs test to test the sequence for randomness above and below the median of 183.5

b- Use the Kruskal-Wallis test to test the claim that the 12 months had priority numbers drawn from the same population.

c- Calculate the 12 monthly means. Then plot those 12 means on a graph. (The horizontal scale lists the 12 months and the vertical scale ranges from 100 to 260). Note any pattern suggesting that the original priority numbers were not randomly selected.

d- Based on the results from arts (a), (b) and (c), decide whether this particular draft lottery was fair. Write a statement either supporting your position that it was fair or explaining why you believe that it was not fair. If you decided that this lottery was unfair, describe a process for selecting lottery numbers that would be fair.

(Q2) New York Company manufactures 0.0109-in. thick aluminum cans for a major beverage supplier. From the data conduct an analysis of the process data for the cans that are 0.0111 in. think. The values in the data a set are measured axial loads of cans, and the top lids are pressed into place with pressures that vary between 158 lb and 165 lb.

a- Based on the given process data, should the company take any corrective actions? Write a report summarizing your conclusions. Address not only the issue of statistical stability, but also the ability of the cans to withstand the pressures applied when the top lids are pressed into place.

b- Also compare the behavior of the 0.0111-in. cans to the behavior of the 0.0109 in. cans and recommend which thickness should be used?

© BrainMass Inc. brainmass.com June 3, 2020, 8:37 pm ad1c9bdddfhttps://brainmass.com/statistics/quantative-analysis-of-data/using-excel-to-solve-statistics-questions-145969

#### Solution Preview

Q1

Refer to excel file for data

Was the draft lottery random?

In 1970, a lottery was used to determine who would be drafted into the US Army. The 366 dates in the year were placed in individual capsules. First the 31 January capsules were placed in a box: then the 29 February capsules were added and the two months were mixed. Then the 31 March capsules were added and the three months were mixed. This process continued until all months were included. The first capsule selected was September 14, so men born on that date were drafted first. The accompanying list show the 366 dates in the order of selection.

a- Use the runs test to test the sequence for randomness above and below the median of 183.5

I don't know how to do a runs test in Excel, so I'm doing the analyses in SPSS. After rearranging the data in Sheet 2, I did the runs test and got the following results:

The two-tailed p-value is p = 0.209. Therefore, we don't have evidence to say that the sequence is not random

b- Use the Kruskal-Wallis test to test the claim that the 12 months had priority numbers drawn from the same population.

The results of the K-W test are below:

The p-value is p = 0.007. Therefore, we can reject the null hypothesis that the means of the 12 months are equal and conclude that at least one month has a different ...