This page includes Step-by-Step instructions to use MS Excel to calculate the
multiple regression equation for the Temperature/Mowing/Water
example.
Example:
Assume that for 7 randomly selected days during a three-hour period spent outside, a person recorded the
outside temperature, the
time they spent mowing the grass, and their water
consumption.
Use
Excel to find the Multiple Regression Equation 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.
| Temperature
(F) |
Water
Consumption (ounces) |
Time
mowing the grass (hours) |
| 75 |
16 |
1.85 |
| 83 |
20 |
1.25 |
| 85 |
25 |
1.5 |
| 85 |
27 |
1.75 |
| 92 |
32 |
1.15 |
| 97 |
48 |
1.75 |
| 99 |
48 |
1.6 |
Preliminaries: Make sure
you have the Data Analysis Tool Pak loaded. Click here for
additional
information on the ToolPak. Use the "back" button on your
browser to return to this page.
Preliminaries: Entering the data.
- If you have already entered the water/temperature data it should be saved
in file exceldata1. If you have this file, open the
file.
- Otherwise enter the Temperature data in column A and water in column
B. Put the category names in Row 1.
- If you need help entering the data, see the Intro to Excel
(link) tutorial.
Procedure·
- From the main menu select Tools>Data Analysis
- From the Data Analysis dialog box, scroll down and select Regression.
Click OK
- 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 (the button at the right end
of the field. It has a small red arrow on the button.
- 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 the small red arrow on it.)
- Mark the check boxes:
- Mark the radio button for New Worksheet. Click in the
field and type the work regression.
- Save your work. Select FILE > SAVE AS. Call the data set ExcelDataRegMulti.
Learn the Procedure for calculating the multiple regression
coefficient
Regression
Tutorial Menu Dictionary
STATS @ MTSU