• We would like learn about the correlation coefficient using Excel. Lets look at the following question.
• To find out how genetically related the cholesterol level is, they measured the cholesterol level of eight mothers and their daughters. They obtained the following data.
•
• Mother Her daughter
• 157 154
• 189 150
• 201 184
• 174 170
• 159 158
• 213 192
• 149 143
• 143 132
•
• Evaluate the correlation coefficient and describe the correlation.
• We will now learn how to use Excel to quickly check our answers.
Open Excel
In cell A1 type Mother
In cell B1 type Her daughter
Below Mother type the data from the exam one by one with pressing Enter in between: 157,189,201,174,159,213,149,143
Below Her Daughter type the data from the exam one by one with pressing Enter in between: 154,150,184,170,158,192,143,132
In cell A11 type Correlation coefficient
In cell A13 begin typing =Cor for correlation coefficient
After you typed =Cor you will only be offered one choice =CORREL, double click on it.
Then highligh all data under Mother
type ,
Then highligh all data under Her daughter
Press Enter.
you should obtain the correlation coefficient. To learn more about correlation coefficient study the textbook.
Exercises
Exercise 1. Is the correlation you obtained in cell A13 weak, moderate or strong? State your result in cell A14.
Exercise 2. Is the correlation you obtained in cell A13 positive or negative? State your result in cell A15. Explain what it means in cell A16.