EXCELcises as of Nov 15, 2009

The assignment is not in final form until it is check-marked
Attach the assigned EXCELcises to the regular homework securely; be sure to label them carefully & include your name. Color coding applies as in the rest of the assigned problems.
SAVE THE TREES: Print compactly, on the fewest sheets possible.

1.1  (a) Using the necessary formulas in column C and in rows 16-19, construct the spreadsheet in this picture (click here);  (b) Print;  (c) Display all formulas [Press CTRL + ` (grave accent)] and print again.

1.2  (a) Last week's payroll for our firm's part-timers looks like this (click here);  using the data on the right of the table (new hours worked), prepare this week's payroll in Excel. (b) Print;  (c) Display all formulas as in 1.1 (c) and print again.

3.  Simulate 900 flips of two fair coins in columns A&B of an Excel sheet as in our class exercise. Place the sum of A and B in column C. Use the COUNTIF( ) function to count the values in columns A and C (the frequency), and bar-graph the respective relative frequencies of A & C. Fit the upper part of the simulation in one page and print normally and, again, in "show-formulas" mode  [CTRL + `]; use proper zoom and column width to fit the page;  (print a total of 2 pages); no need to show all 900 lines and there is no need to show the graphs on the second sheet.(save the file and modify to use in 4, below)

4.  Repeat the previous exercise for 900 rolls of two fair dice in columns A&B. Use the FREQUENCY function to count the values in columns A and C (the frequencies), and do a column graph of the respective relative frequencies of A & C. Fit the upper part of the simulation in one page and print normally and, again, in "show-formulas" mode  [CTRL + `]; use proper zoom and column width to fit the page;  (again, print a total of 2 pages); no need to show all 900 lines and there is no need to show the graphs on the second sheet.
Comment: (a) What proportion of "3"s would you expect in Col. A? What did you get? (b) What proportion of "3"s would you expect in Col. C? What do you actually get? (Hand-print these answers and circle the values very neatly on the first sheet)
 

5. Why Does Sampling Work? Sampling is used widely in business, manufacturing, science, social research and elsewhere. Why? In order to represent a much larger set,  such as the population of a state, the totality of items manufactured, the quality of the corn grown in large area, etc. It may be too expensive or even impossible to check every one of millions of items or persons; so we examine a few. How few? If we ask only one person how many cups of coffee they drink in one day, we learn next to nothing about the rate of coffee consumption in NJ. But if we ask five, fifty, or 500 randomly selected folks the same question, we begin to get an estimate of the answer with an increasing sense of accuracy.

In order to see the effect of increasing sample size n on the accuracy of the estimate, we use Excel to simulate random observations, =5+10*RAND(), and plot the frequency of the average (for n=1, 2, ...5) on a comparative graph, like this.

Generate 1000 sets of five-tuples in columns A-E (the example is for 500 sets of observations). Column G (AVERAGE($A1:A1)) is identical to A (the average of one number equals the number itself), col. H=AVERAGE($A1:B1), ..., K=AVERAGE($A1:E1). These are averages of random samples of a single observation, 2 observations, etc. Show the respective frequency distributions (N-R) with bins in M. Add a combined graph of the frequencies. (the column graph is for n=1; show it similarly or via a line graph.
Using "Landscape" orientation, print three (or four) single pages to display
(a) the graph,
(b) columns A-R (as in the example), and
(c) the formulas in columns (A-R) [Press CTRL + ` (grave accent)]; use appropriate zoom, e.g. 50% to help fit these columns; if necessary, widen columns so the equations show entirely. You may need 2 pages for Part (c)-- no need to show all 1,000 samples, however (see example)
(d) Explain, briefly, what the comparative graph tells us about sampling and the relative precision of the procedure.

 6: Another Sampling Simulation?

7.  Descriptive statistics: A. Using the following 25x10 block of data (Set A) find its quartiles, median, mean, etc as in the example. Some applicable formulas are given. Fill in the rest and also do the relative frequency and graph with proper x-axis (bins provided).  (a) Comment briefly: Is the distribution symmetric/skewed (b) What are the outliers (if any)? B. Repeat for Set B. [Hints: cut-and -paste the data from here into Excel, do not retype. You can use the same sheet for both parts A and B and change the data or, alternatively,  you can copy-and-paste the worksheet (A) in Sheet 1 into Sheet 2 (B) and then change the data. Print each of A & B in one page, if possible. No need to print the formulas. ]

(the following is an example; your data will be Set A and Set B, respectively. In the formulas, "D" indicates the data block)

8.  Probability meets Statistics (and Normality and Simulation and Excel)...