This page includes Step-by-Step instructions to use MS Excel to calculate the regression equation for the Water/Temperature/Time Mowing the grass example.  

Example: Assume that during a three-hour period spent outside, a person recorded the outside temperature, the time spent mowing the grass, and their water consumption.  

Use Excel to find the Multiple Correlation Coefficient, R if the amount of  the amount of water consumed is dependent on the temperature and the time spent mowing the grass.  Use the data given below.

Preliminaries: Make sure you have the Data Analysis ToolPak loaded.  
If you need additional information about MS ToolPak click the link below.  You will need to use your Internet browsers' "back" button to return to this page.

MS ToolPak Information

Preliminaries: Entering the data. 

• Enter the temperature data in column A, the time they spent mowing the grass, in column B and water consumption in column C.  Put the category names in row 1.
• If you need help entering the data, see the Intro to Excel tutorial.

Procedure· 

1. From the main menu select Tools>Data Analysis
2. From the Data Analysis dialog box, scroll down and select Regression. Click OK
3. The Regression dialog box should appear.
   • Put your cursor in the field for the Input Y Range. 
   • Click the Collapse Dialog button (the button at the right end of the field. It has a small red arrow on the button. This will Collapse the dialog box and take you to the spreadsheet.
   • Highlight the range for the Water Consumption including the title. (i.e. Cells C1-C8)
   • Press the Return to Dialog button. (The button at the end of the field, with the small red arrow on it.)
   • Click in the field for the Input X Range. 
   • Click the Collapse Dialog button.
   • Highlight the range for the Temperature and Time Mowing including the title. (i.e. Cells A1-B8)
   • Note: You have to enter temperature and time mowing in the X range.
   • Press the Return to Dialog button. 
   • Mark the radio button for New Worksheet.  Click in the field box and type the word multicorrelation.

    

4. Save your work. Select FILE > SAVE AS. Call the data set ExcelmultiData.
   

• Click here to check and interpret your answers.  

Learn the Procedure for calculating the multiple regression coefficient

  

Regression Tutorial Menu    Dictionary

STATS @ MTSU