This page includes Step-by-Step instructions to use MS Excel to calculate a correlation coefficient for the Water/Temperature example.  

Note: If you have never used Microsoft Excel, you may want to try the Introduction to Excel tutorials.

Example: Assume that during a three-hour period spent outside, a person recorded the temperature and their water consumption.  Use MS Excel to calculate the correlation coefficient between Temperature and Water Consumption.

Temperature (F) Water Consumption (oz)
99

48
85 27
97 48
75 16
92 32
85 25
83 20

 

Preliminaries:  Entering Data

If you have already entered and saved the data from a previous tutorial, then you can open the file ExcelData1 and skip to Procedures.

  1. Enter “Temperature (F)” in cell A1
  2. Enter “Water Consumption (ounces)” in cell B1
  3. Enter the data shown below in the appropriate columns. Do not change the order of the items. (See Excel graph below to verify location of items.)

  1. Save the data. Select FILE > SAVE AS. Call the data set ExcelData2. (You will use the data again in future tutorials.)
 

Procedure:  Correlation Coefficient

  1. Enter “Correlation of temperature and water consumption” in cell A10. 
  2. Put your cursor in cell A11. 
  3. Select INSERT > FUNCTION. (The shortcut for this is to click the function icon .)   
  4. In the left menu box, select STATISTICAL. 
  5. In the right menu box, select CORREL. 
  6. Click OK. 
  7. In the Array 1 box, enter the cell locations of the temperature data.  (i.e. enter A2:A8).
  8. In the Array 2 box, enter the cell locations of the water consumption data.  (i.e. enter B2:B8). 
  9. Click OK.

Learn the Procedure for calculating correlation coefficients

  

Correlation Menu  Dictionary 

STATS @ MTSU