Excel Activity 2: Computer Simple Descriptive Statistics and Convert Raw Scores to Standard Scores
Purpose: The purpose of this activity is to learn how to use Excel to compute simple descriptive statistics and convert raw scores to standard scores.
Problem: The data are taken from a reading proficiency test administered to eight 5th grade classes from the same school district. The scores from the eight different classes have been combined to determine the overall results of the test within the district. In this exercise, you will calculate the mean and standard deviation of the test scores so that you can convert the raw scores to standard scores and compare different students' performance.
Calculate the Mean
Calculate Standard Deviation
Calculate Standard Scores
MEAN (Back to Top) (Back to Main Page)
Place the cursor in cell E2 to the right of the label mean. Select Insert from the menu bar and scroll down to Function. A dialog box titled Insert Function will appear displaying an alphabetical list of functions to choose from. Under Or select a category select statistical. This will change the available functions in the list so that only functions pertaining to statistical procedures will be displayed. Scroll down to the function named AVERAGE and highlight it. This is the function that calculates the arithmetic mean of the selected data. Click OK and a new dialog box titled Function Arguments will appear. In the field labeled Number1 specify the range of cells containing the values of the variable you want to calculate the mean for. The shorthand method for specifying a range is the label for first cell containing data, followed by a colon, then the label for the last cell containing data (e.g., A2:A23). This notation says, "I'm specifying a range of values that begins with cell A2 and ends with cell A23, and I want to include the values of all the cells in between." In the case of the current variable of interest, the cell range to specify is cell B2 through cell B201 (i.e., B2:B201). The field labeled Number2 should be left blank. Once a valid range is specified the results of the function will be displayed at the bottom of the dialog box, in this case 75.175. However, our goal is to have the mean displayed on the spreadsheet. Clicking on OK will close the dialog box and insert the value in the pre-selected cell of the spreadsheet (in this case, E2).
STANDARD DEVIATION (Back to Top) (Back to Main Page)
Now place the cursor in cell E4 to the right of the label standard deviation. Select Insert from the menu bar and scroll down to Function. In the Insert Function dialog box, be sure that statistical is still selected. Scroll down to the function named STDEV and highlight it. This function calculates the standard deviation of the selected data. In the field labeled Number1 specify the range of cells containing the values of the variable you want to calculate the standard deviation for, just as you did for the mean (i.e., cell B2 through cell B201 or B2:B201). The field labeled Number2 should be left blank. Once a valid range is specified the results of the function will be displayed at the bottom of the dialog box, in this case 10.80546926 Clicking on OK will close the dialog box and insert the value in the pre-selected cell of the spreadsheet (E4).
STANDARD SCORES (Back to Top) (Back to Main Page)
Now that we have calculated the mean and standard deviation of the data set, we are ready to transform the scores to standard scores, or z-scores. Place the cursor in cell E8 to the right of the label z-score7. Select Insert from the menu bar and scroll down to Function. In the Insert Function dialog box, be sure that statistical is still selected. Scroll down to the function named STANDARDIZE and highlight it. This function calculates the z-score of the selected data point. In the field labeled X specify the cell that contains the raw score that you wish to standardize. For this example, choose cell B8. In the Mean and Standard_dev fields specify the cells that contain the mean and standard deviation that you previously calculated for the distribution of scores (E2 for the mean and E4 for the standard deviation). Once valid fields are specified the results of the function will be displayed at the bottom of the dialog box, in this case -1.959653754 Clicking on OK will close the dialog box and insert the value in the pre-selected cell of the spreadsheet (E8). Now repeat this same procedure for ID numbers 11 and 18, placing their z-scores in the z-score11 and z-score18 fields, respectively.
Questions1. Compare the raw scores of ID numbers 7, 11, and 18. How do they relate to one another? What additional information is needed to interpret the scores in terms of the distribution besides the raw scores?
2. Interpret the three z-scores that you calculated. Write a statement about what each one means.
3. Compare the z-scores of ID numbers 7, 11, and 18. How do they relate to one another? In what ways do the z-scores provide different information from the raw scores? Which one do you feel is more useful? Why?
Question 1 Answer: The raw scores of ID numbers 7, 11, and 18 are 54, 76, and 98, respectively. These scores appear to represent low, medium, and high values. However, in order to properly assess how they fit with the distribution, information about the center (e.g., the mean) and the variability (e.g., standard deviation) is necessary. (Back to Questions)
Question 2 Answer: The z-score of –1.96 for ID 7 indicates that this person's score was just about 2 standard deviations less than the mean score. The z-score of 0.08 for ID 11 indicates that this person's score was just barely above the mean score. Finally, the z-score of 2.11 for ID 18 indicates that this person's score was over two standard deviation above the mean score. (Back to Questions)
Question 3 Answer: As it appeared with the raw score values, the three z-scores seem to represent low, medium, and high scores on the reading test. The z-scores, however, provide information about the location of the scores relative to the other scores in the distribution, since they are expressed in terms of number of standard deviations from the mean. In this regard, the z-scores are a more useful statistics than the raw scores. However, the z-scores are not in the original units of the variable, and therefore make extrapolation more difficult than the raw scores. The usefulness of a statistic always depends on what information it is that you are seeking. (Back to Questions)