Part a) Ways to compute them.

Part b) Effect of a Linear Transformation.
*************************************
 

The data for  Exercise 1.74 is stored on the CD at:
F:\PCDataSets\Excel\ CHAP01\EX01_027.XLS.  If you do not have the CD read note .

Load the data.

Part (a).  Ways to compute the mean and the standard deviation.
**************************************************************************

In part (a) of this exercise, you will create a  worksheet for computing the standard deviation of a data set
following the pre-Excel era computational practices.
Organize your worksheet as below:
 

As  seen in the above picture, you should compute the value of Xbar in the cell G1 using the formula for the mean
from page 41 in your textbook.  Also do observe that the SUM and COUNT functions are over the range A1:A1000 .  Excel automatically ignores
labels (nonnumerical entries) and the empty cells, so you may use this worksheet to make the same computations (in this case the mean and standard deviation) for any data set
with as many as 999 data points without any further changes!

Next
Fill the columns B2:B30 and C2:C30 according to the formulas shown in B1 and C1.
For  column  C, you may use just (X-Xbar)*(X-Xbar) or, (X - Xbar)^2 or, and this is more Excelish, use the function "POWER".

Be sure that you use the appropriate absolute and relative references , so you can drag down your formulas.

Here are the numbers for the first several rows:
 

Next
In the cell G2, compute the value of the standard deviation using the  formula for s
from page 51 in your textbook.  In the image above, the formula bar shows s written in Excel language.

To check that your results are correct, open the "Descriptive Statistics" window from the "Data Analysis" menu,
and fill it in like below:
 

Save the worksheet.  You will have to hand it in .

Note.  You can also check your results using the "AVERAGE" and "STDEV" from the "Function wizard".

Part (b).  Effect of a Linear Transformation.
**************************************************
 In part (b) of this exercise you will create an interactive worksheet ;  you need to organize the sheet with a few labels.

*Note*  The X data in column A is the same as from part (a) above.

Your Excel worksheet should look  like:
 
 

To begin with, enter 0 and 1 in the cells B2 and C2 (respectively), and then enter in cell D2 the linear transformation formula:
 

and drag it down to cell D30.

Notice the appropriate use of relative and absolute references in the above formula bar.
Would it work if we used "A2" instead of "$A2" ?  What about "C2" instead of "$C$2"?
 

Change the font color in the cells B2 and C2 to red.  The values from these cells will be your
Interactive Input values
for the Linear Transformation of the data.

Now, use the functions "AVERAGE" and "STDEV" to enter the mean and the standard deviation of the X and of
the Xnew data sets, into the corresponding cells of column G (as shown below).

Note.  These two functions give you a  third alternative way to compute Xbar and s.  Compare the results
with those from part (a)!

The resulting worksheet should look like:
 

As you see, the background of the output cells was changed to yellow.

We will always use red for  "Interactive Input" and yellow for the output.

Also please observe that for a = 0 and b = 1 the Linear Transformation leaves the data unchanged.

Now input a = 1 and observe that Xnew bar = Xbar +1, but that the standard deviation remains unchanged.
Next, input b = -4 and observe that s of Xnew  is  now equal to 4 times the original s .
 
 
 
 

Use the "Interactive Worksheet" you just created to answer question 1.74.b from the textbook.
 
 
 
 

Question.   Hand in two Excel worksheets.

The first  worksheet should show the all the computations from part (a) .

The second  worksheet should show all the computations from part (b) .