This page includes Step-by-Step instructions to use MS Excel to test the
model 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 MS Excel to test 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.
- Check to see if you have the Data Analysis ToolPak
- On the main menu, select Tools.
- Near the bottom, look for Data Analysis.
- If it is there, skip to Entering Data.
- If it is not there, then you must "install it."
Installing the Data Analysis Tool
Pak
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,
Time Mowing in column B and Water in column C. 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 testing the multiple regression
equation.
Regression
Tutorial Menu Dictionary
STATS @
MTSU