CALCULATE STANDARD DEVIATION AND VARIANCE

The sample you have collected contains test scores as well as attendance information of sixth-grade through eighth-grade students across the district. You will calculate the standard deviation of the test scores within the sample. Refer to Figure 8.10 as you complete Step 1.

a. Open e08h1Assessment and save it as e08h1Assessment_LastFirst.

b. Click the Test Scores worksheet tab. Click cell H9, click the Formulas tab, click More Functions in the Function Library group, point to Statistical, and then select STDEV.S. STDEV.S is being used because the data is a random sample of 50 test scores. If every test score in the population were included in the dataset, STDEV.P would be used.

c. Select the range C4:C53 and click OK. (On a Mac, click Done.) With cell H9 still selected, click Decrease Decimal in the Number group on the Home tab until no decimal points are displayed. The standard deviation for the sample is 181; therefore, assuming the distribution is normal, about 66% of students will receive a test score between 336 and 698. This is calculated by adding the standard deviation, 181, to the average test score of 517 to determine the high end of the range, and subtracting 181 from 517 to determine the low end of the range.

d. Tab to cell I9, click the Formulas tab, click More Functions in the Function Library group, point to Statistical, and then select VAR.S.

e. Select the range C4:C53 and click OK. With cell I9 still selected, click Decrease Decimal located in the Number group on the Home tab until no decimal points are displayed. The results of the VAR.S function (32803) would indicate a large dispersion. The larger the variance, the greater the dispersion of data around the mean test score. This means the data observations are somewhat spread out from the average and from each other.

f. Save the workbook.

USE THE CORREL FUNCTION After calculating the standard deviation and variance to help determine the data points’ distance from the mean, you theorize that there is a direct relationship between attendance and test scores. You will use the CORREL function to test the strength of the relationship. Refer to Figure 8.11 as you complete Step 2.

a. Click cell J9, click the Formulas tab, click More Functions in the Function Library group, point to Statistical, and then select CORREL.

b. Click the Array1 box and select C4:C53, click in the Array2 box and select F4:F53, and then click OK. (On a Mac, click Done.)

c. Ensure cell J9 is selected and click Decrease Decimal in the Number group on the Home tab until two decimal positions are displayed. The result is –0.37. This means that there is a slightly negative correlation between attendance and test scores. Thus, the more days a student is absent, the lower the test scores received.

d. Save the workbook.

USE THE FREQUENCY FUNCTION You want to determine the frequency of student absences based on the criteria of perfect attendance. Attendance is divided into the following bins: 0 days absent, 1 to 5 days absent, and 6 to 10 days absent. To do this, you will use the FREQUENCY function. Refer to Figure 8.12 as you complete Step 3.

a. Select the range I12:I14, type =FREQUENCY(F4:F53,H12:H14)/I$5, and then press Ctrl+Shift+Enter. Refer to Figure 8.12 to check the function. The range I12:I14 was selected in order to return all results. If only I12 were selected, the function would return data just for students with 0 to 4 absences. You divided the FREQUENCY function by the number of data points in the sample (50) so that the results are calculated as percentages.

b. Ensure the range I12:I14 is still selected and apply the Percentage Number Format in the Number group on the Home tab. From the results, you can determine that 14% of the students had perfect attendance, 50% missed between 1 to 5 days, and 36% missed between 6 and 10 days.

c. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook, and exit Excel.

CALCULATE STANDARD DEVIATION AND VARIANCE

The sample you have collected contains test scores as well as attendance information of sixth-grade through eighth-grade students across the district. You will calculate the standard deviation of the test scores within the sample. Refer to Figure 8.10 as you complete Step 1.

a. Open e08h1Assessment and save it as e08h1Assessment_LastFirst.

b. Click the Test Scores worksheet tab. Click cell H9, click the Formulas tab, click More Functions in the Function Library group, point to Statistical, and then select STDEV.S. STDEV.S is being used because the data is a random sample of 50 test scores. If every test score in the population were included in the dataset, STDEV.P would be used.

c. Select the range C4:C53 and click OK. (On a Mac, click Done.) With cell H9 still selected, click Decrease Decimal in the Number group on the Home tab until no decimal points are displayed. The standard deviation for the sample is 181; therefore, assuming the distribution is normal, about 66% of students will receive a test score between 336 and 698. This is calculated by adding the standard deviation, 181, to the average test score of 517 to determine the high end of the range, and subtracting 181 from 517 to determine the low end of the range.

d. Tab to cell I9, click the Formulas tab, click More Functions in the Function Library group, point to Statistical, and then select VAR.S.

e. Select the range C4:C53 and click OK. With cell I9 still selected, click Decrease Decimal located in the Number group on the Home tab until no decimal points are displayed. The results of the VAR.S function (32803) would indicate a large dispersion. The larger the variance, the greater the dispersion of data around the mean test score. This means the data observations are somewhat spread out from the average and from each other.

f. Save the workbook.

USE THE CORREL FUNCTION After calculating the standard deviation and variance to help determine the data points’ distance from the mean, you theorize that there is a direct relationship between attendance and test scores. You will use the CORREL function to test the strength of the relationship. Refer to Figure 8.11 as you complete Step 2.

a. Click cell J9, click the Formulas tab, click More Functions in the Function Library group, point to Statistical, and then select CORREL.

b. Click the Array1 box and select C4:C53, click in the Array2 box and select F4:F53, and then click OK. (On a Mac, click Done.)

c. Ensure cell J9 is selected and click Decrease Decimal in the Number group on the Home tab until two decimal positions are displayed. The result is –0.37. This means that there is a slightly negative correlation between attendance and test scores. Thus, the more days a student is absent, the lower the test scores received.

d. Save the workbook.

USE THE FREQUENCY FUNCTION You want to determine the frequency of student absences based on the criteria of perfect attendance. Attendance is divided into the following bins: 0 days absent, 1 to 5 days absent, and 6 to 10 days absent. To do this, you will use the FREQUENCY function. Refer to Figure 8.12 as you complete Step 3.

a. Select the range I12:I14, type =FREQUENCY(F4:F53,H12:H14)/I$5, and then press Ctrl+Shift+Enter. Refer to Figure 8.12 to check the function. The range I12:I14 was selected in order to return all results. If only I12 were selected, the function would return data just for students with 0 to 4 absences. You divided the FREQUENCY function by the number of data points in the sample (50) so that the results are calculated as percentages.

b. Ensure the range I12:I14 is still selected and apply the Percentage Number Format in the Number group on the Home tab. From the results, you can determine that 14% of the students had perfect attendance, 50% missed between 1 to 5 days, and 36% missed between 6 and 10 days.

c. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook, and exit Excel.

CALCULATE STANDARD DEVIATION AND VARIANCE

The sample you have collected contains test scores as well as attendance information of sixth-grade through eighth-grade students across the district. You will calculate the standard deviation of the test scores within the sample. Refer to Figure 8.10 as you complete Step 1.

a. Open e08h1Assessment and save it as e08h1Assessment_LastFirst.

b. Click the Test Scores worksheet tab. Click cell H9, click the Formulas tab, click More Functions in the Function Library group, point to Statistical, and then select STDEV.S. STDEV.S is being used because the data is a random sample of 50 test scores. If every test score in the population were included in the dataset, STDEV.P would be used.

c. Select the range C4:C53 and click OK. (On a Mac, click Done.) With cell H9 still selected, click Decrease Decimal in the Number group on the Home tab until no decimal points are displayed. The standard deviation for the sample is 181; therefore, assuming the distribution is normal, about 66% of students will receive a test score between 336 and 698. This is calculated by adding the standard deviation, 181, to the average test score of 517 to determine the high end of the range, and subtracting 181 from 517 to determine the low end of the range.

d. Tab to cell I9, click the Formulas tab, click More Functions in the Function Library group, point to Statistical, and then select VAR.S.

e. Select the range C4:C53 and click OK. With cell I9 still selected, click Decrease Decimal located in the Number group on the Home tab until no decimal points are displayed. The results of the VAR.S function (32803) would indicate a large dispersion. The larger the variance, the greater the dispersion of data around the mean test score. This means the data observations are somewhat spread out from the average and from each other.

f. Save the workbook.

USE THE CORREL FUNCTION After calculating the standard deviation and variance to help determine the data points’ distance from the mean, you theorize that there is a direct relationship between attendance and test scores. You will use the CORREL function to test the strength of the relationship. Refer to Figure 8.11 as you complete Step 2.

a. Click cell J9, click the Formulas tab, click More Functions in the Function Library group, point to Statistical, and then select CORREL.

b. Click the Array1 box and select C4:C53, click in the Array2 box and select F4:F53, and then click OK. (On a Mac, click Done.)

c. Ensure cell J9 is selected and click Decrease Decimal in the Number group on the Home tab until two decimal positions are displayed. The result is –0.37. This means that there is a slightly negative correlation between attendance and test scores. Thus, the more days a student is absent, the lower the test scores received.

d. Save the workbook.

USE THE FREQUENCY FUNCTION You want to determine the frequency of student absences based on the criteria of perfect attendance. Attendance is divided into the following bins: 0 days absent, 1 to 5 days absent, and 6 to 10 days absent. To do this, you will use the FREQUENCY function. Refer to Figure 8.12 as you complete Step 3.

a. Select the range I12:I14, type =FREQUENCY(F4:F53,H12:H14)/I$5, and then press Ctrl+Shift+Enter. Refer to Figure 8.12 to check the function. The range I12:I14 was selected in order to return all results. If only I12 were selected, the function would return data just for students with 0 to 4 absences. You divided the FREQUENCY function by the number of data points in the sample (50) so that the results are calculated as percentages.

b. Ensure the range I12:I14 is still selected and apply the Percentage Number Format in the Number group on the Home tab. From the results, you can determine that 14% of the students had perfect attendance, 50% missed between 1 to 5 days, and 36% missed between 6 and 10 days.

c. Save the workbook. Keep the workbook open if you plan to continue with the next Hands-On Exercise. If not, close the workbook, and exit Excel.

CALCULATE STANDARD DEVIATION AND VARIANCE

CALCULATE STANDARD DEVIATION AND VARIANCE

a. Open e08h1Assessment and save it as e08h1Assessment_LastFirst.

a. Open e08h1Assessment and save it as e08h1Assessment_LastFirst.

f. Save the workbook.

f. Save the workbook.

d. Save the workbook.

d. Save the workbook.