User:ASnieckus/StatisticsContent/Learn by doing/Case III - Linear relationship

Description of activity and Excel instructions:Case III - Linear relationship

Dataset: baby-crying-IQ.xls

Create a scatterplot
Use the Chart Wizard to create a scatterplot.


 * Select the columns containing the two variables to be plotted.
 * Select Insert > Chart....

The Chart Wizard displays.

In ''1. Chart Type'':
 * Select XY (Scatter).
 * Select the Points Only version.

In ''2. Data Range'':
 * Click 'Data series in columns.
 * Check First row as label.

No changes are needed for ''3. Data Series''.

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.
 * Uncheck the Display Legend box.

Calc creates a scatterplot. Note that the X axis scale is shown as 5 to 35, a reasonable choice for this data.

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...
 * Explore the options in the Icon section, in the Line tab.

Create a correlation coefficient
Use a formula to compute the correlation:
 * Click in a cell outside of the first two columns of data.
 * Type "=CORREL([range1],[range2])" where [range1] is the first column of data and [range2] is the second column of data.

Return to the OLI's Case III - Linear relationship page to complete the first interpretive exercise.

Calculate p-value
Calc does not include a formula or data analysis routine to calculate the "t-test for the slope" of a regression line. You can use Excel for this activity, or the web-based p-value calculator for the correlation coefficients at danielsoper.com.

In fact, this t-test is solely dependent on the value of the correlation coefficient, r, and the size of the sample:


 * $$t = \frac{r}{\sqrt{\frac{1-r^2}{n-2}}}$$

Return to the OLI's Case III - Linear relationship page to complete the next interpretive exercise.

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 (R2).
 * Click OK.

The regression line displays among the data points and the regression equation and R2 display underneath. You can select and re-position the equation and R2 value, if they are displayed on top of the data points.

Return to the OLI's Case III - Linear relationship page to complete the interpretive exercises.