StatTutor: Is students' college GPA related to their high school GPA? (Question 1)

Description of activity and Excel instructions: StatTutor -- Is students' college GPA related to their high school GPA? (Question 1)

Dataset: Direct link for body_image.xls not available.

Prep data

If your data contain empty cells or cells with non-numerical data (e.g., asterisks), you should sort the data first so that all of the numerical data are grouped together -- Calc cannot create a scatterplot with non-numerical data.

To sort your data:

• Type Ctrl+A to select all of the data.
• Select Data > Sort....

The Sort dialog displays.

• Click the Options tab.
• Check Range contains column labels.
• Click the Sort Criteria tab.
• Under Sort by, in the drop down menu, choose one of the variables to be plotted.
• Under Then by, choose the second variable to be plotted.
• Click OK.

Create scatterplot

Use the Chart Wizard to create a scatter plot.

• Select the columns including the two GPA variables.
• Select Insert > Chart....

The Chart Wizard displays.

In 1. Chart Type:

• Select XY (Scatter).
• Select the Points Only version.
• Click Next>>.

In 2. Data Range:

• Click 'Data series in columns.
• Check First row as label.

In 3. Data Series:

• Check to be sure that the assignment of variables to the X and Y axis is in accordance with classification of response and explanatory variables. Respecify as needed.
• Check to be sure that the specified range does not include missing or non-numeric data.
• Click Next>>.

In 4. Chart Elements:

• Enter a title for the chart.
• Enter a title for the X axis (explanatory variable).
• Enter a title for the Y axis (response variable).
• Decide if you want to display a grid in the chart background.
• Decide if you want to display the legend .
• Click Finish.

Calc creates a scatterplot. Note that the X axis scale is 1 to 4.5, while the Y axis scale is 0 to 5. Decide if this is a reasonable choice for this data. Adjust, if necessary.

If you want to change the shape or size of the data points, double click on the graph to enter edit mode, right click on the data points, select Object Properties..., and explore the options in the Icon section.

Create the correlation

• Click in an empty cell in the worksheet.
• Use the function wizard to create a correlation between college GPA and high school GPA.

Report results and draw conclusions

Move to the next steps in the StatTutor exercise for Question 1 to

1. report your results (remember that reporting results means that you should describe the results of your data analysis, listing and describing your numerical results), and
2. draw conclusions (describe what the results mean as relates to the question asked).

Plot the least squares regression line

• In edit mode on the graph, right-click one of the points on the graph.
• Select Insert Trend Line....

The Trend Line dialog displays.

• Click on the Type tab.
• Verify that Linear is selected under Regression Type.
• Check the Show equation box.
• Check the Show correlation coefficient (R²).
• Click OK.

The regression line displays among the data points and the regression equation and R² display nearby.

Return to the StatTutor page to complete the interpretive exercises. (Be sure to include the data, that is the correlation and the form and direction of the graph, in your description of the results, but save your thoughts on what the results mean for the conclusions section.)