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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- introduction to excel
- count and sum your data in excel 2002
- ms excel mcq quiz set 1 e library wcl
- sum cells across columns in excel piratepanel
- introduction to spreadsheets with microsoft excel
- excel assignment 2
- excel intro el camino college
- excel tutorial henderson state university
- randgen add in kelley school of business
- useful websites
Related searches
- excel lookup 2 criteria and return value
- 2 01 english 3 assignment advertising
- assignment 2 measurement
- assignment 2 01 english 3 flvs
- maths literacy grade 10 assignment 2 memo
- assignment 2 maths literacy grade 10
- mathematical literacy assignment 2 2021
- 2019 assignment 2 mathematics literacy grade 10
- mathematical literacy grade 10 assignment 2 2019
- mathematical literacy assignment 2 grade 10
- mathematical literacy grade 10 assignment 2 2019 memo
- memorandum assignment 2 mathematical literacy grade 10