Correlation coefficient

  • 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

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.