This page includes Step-by-Step instructions to use MS Excel to test each B(k)
within 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 MS Excel to
test each B(k) to determine if each parameter should be dropped from the model
with the amount of
water
consumed being 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.
- 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
ExcelDataRegMultiTest.
Regression
Tutorial Menu Dictionary
STATS @
MTSU