7.4 Standard Scores
The standard score (z) indicates the location of a value in the distribution as a function of how far the value is from the mean of the distribution in terms of the standard deviation.
7.4.1 Calculating the Mean and Standard Deviation
In this part of the lab, you will compute the mean and the standard deviation of the distribution of Hawaii Measures.
- In an empty cell, type Mean and next to the cell, compute the mean using the function
=AVERAGE(C4:C21). - In another empty cell type Stand Dev and next to the cell, compute the standard deviation using the function
=STDEV.S(C4:C21). - In the
Numberribbon, adjust the standard deviation value to have two (2) decimal places.
The result for the mean is 391.51, and the standard deviation is 11.78.
7.4.2 Converting to Z-Scores
The function STANDARDIZE(x, mean, stand_dev) returns a normalized value x from a distribution characterized by mean and standard deviation. The normalized value is the same as the z-score of the value.
- Go to the top of an empty column and type Z-Scores as the column’s header.
- Below Z-Scores, use the function
= STANDARDIZE(C4, mean, stand_dev)to convert the value in cell C4 in the data set. In the formula, replace mean with the cell address containing the mean value from calculations in Section 7.4.1, and replacestand_devwith the cell address with the standard deviation calculated. Include$in the mean and standard deviation addresses since they are fixed for all data values. - Adjust the z-score to have two (2) decimal places.
- Select the cell containing the formula in Step 2.
- Position the mouse pointer in the lower right corner of the selected cell until it becomes a
+sign and click-drag downward across the range that covers all the Hawaii measures.
As a result, the z-scores range from -1.55 to 1.69.