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

Example: Assume that for 7 randomly selected days during a three-hour period spent outside, a person recorded the outside temperature, whether they were in the sun or not, and their water consumption.  The number 1 indicates the sun was present and the number 0 indicates that the sun was not present.

Use Excel to find the Multiple Regression Equation if the amount of  the amount of water consumed is dependent on the temperature and the presence of sun.  Use the data given below.

Preliminaries: Make sure you have the Data Analysis Tool Pak loaded.  (Click here for additional information.  Use the back button on your browser to return to this page.)

Preliminaries: Entering the data. 

• Enter the Temperature data in column A, Sun data in column B and Water data 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 with a small red arrow on it. 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 (The button at the right end of the field with a small red arrow on it.)
   • Highlight the range for the Temperature and Time Mowing excluding the title. (i.e. Cells A1-B8)
   • Press the Return to Dialog button. (The button at the end of the field, with a small red arrow on it.)
   • Mark the check boxes:
     •  Labels
     • Confidence Level
   • Mark the radio button for New Worksheet.  Click in the field and type the word regression.

   

    

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

• Click here to check your answers.  put the answer for the mowing example here
• Click here to learn how to interpret the output.

Learn the Procedure for Modeling with Indicator Variables

  

Regression Tutorial Menu    Dictionary

STATS @ MTSU