Excel Assignment #2



Excel Assignment #2

PSYCH 315

Dr. Little

SP 2003

Due Friday, May 1

It was hypothesized that the tempo of country-western music played in bars was related to the consumption of alcohol. Observers visited bars and recorded the tempo (beats per minute) of each song and the average number of sips of alcoholic beverages while each song was playing. The following data were obtained:

|Tempo (X) |# Sips per song |

| |(Y) |

|35 |1.150 |

|38 |1.150 |

|44 |1.000 |

|48 |1.075 |

|51 |0.950 |

|64 |0.975 |

|68 |0.950 |

|68 |0.925 |

|72 |0.875 |

|80 |0.900 |

|85 |0.725 |

|91 |0.725 |

|93 |0.875 |

|100 |0.525 |

|102 |0.800 |

|108 |0.775 |

|112 |0.750 |

|118 |0.625 |

In Excel:

1) use the “insert function” command to find the mean and standard deviation for Tempo and # of Sips

2) create columns of deviation scores for both Tempo and # of Sips

3) create a column of cross-products (X – MeanX)(Y – MeanY)

4) find the sum of the cross-products “by hand” ((X – MeanX)(Y – MeanY)

5) find the covariance “by hand” [((X – MeanX)(Y – MeanY)] / n

6) compute the correlation between Tempo and # of sips (compute Pearson’s r) “by hand”

7) create a scatterplot of the data

8) add a trendline and R2 to the graph

NOW:

You have just learned that a research assistant made an error. The # of Sips corresponding to Tempo = 44 should be .400.

9) copy the two columns of original data to another location on your Excel worksheet and make the above change to the incorrect # of Sips score

10) this time, to find Pearson’s r for the updated data set, use Insert / Function (or the fx key). Paste the resulting r and label it.

11) make a scatterplot of the updated data; add a trendline and the R2 value

12) Answer this question: What principle does this assignment demonstrate? (Look at the plotted data and changes in r and R2). Type your response into Excel or handwrite it after you print out your work.

*Make sure to label:

• Mean for Tempo and # of Sips

• S for Tempo and # of Sips

• Sum of cross-products

• Pearson’s r

• The “updated” or “new” Pearson’s r

• The axes of the graphs with meaningful labels (not “X” or “score”)

PSYCH 315

Dr. Little

SP 2003

Correlations

- use the “insert function” command to find the mean and standard deviation for both variables

- create a column of deviation scores for both variables.

- sum the deviation scores for each variable and you should get 0

- in a new column, multiply the deviation scores of the first variable (x) with the deviation scores with the second variable (y) – these are the scross-products

- sum the cross-products

- divide the cross-products by n to get the covariance

- to compute the correlation between x and y: Pearson’s r = [the covariance / (SX * SY)]

Create a scatterplot of the data

- Insert / Chart. Select “XY (Scatter)” You don’t want to connect the lines on this graph, so just select the first chart option and select “Next.” Select both columns of data and select series in: Columns. Click “Next.” Title your axes. Click “Next” and then “Finish.”

Add a trendline and R2 to the graph

- click on any data point to select it, then right-click and select “Add Trendline.” Under Type, select “Linear.” Under Options, select “Display R-squared value on chart.”

To find a correlation using the Excel function

- go to Insert / Function or use the fx key - make sure you are in the Statistical category and select “CORREL” – Array 1 should be the scores for one variable and Array 2 should be scores for the other variable.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download