This page includes Step-by-Step instructions to use MS Excel to calculate the
regression equation for the Water/Temperature
example.
Example:
Assume that during a three-hour period spent outside, a person recorded the
temperature and their water consumption.
Estimate
the amount of water needed based on a given temperature
of 95 degrees Fahrenheit. To do that, use MS Excel to find the Least Squares Linear
Regression Equation for the line of best fit for the temperature and the amount of water
consumed.
| Temperature (F) |
Water Consumption (oz) |
| 99 |
48
|
| 85 |
27 |
| 97 |
48 |
| 75 |
16 |
| 92 |
32 |
| 85 |
25 |
| 83 |
20 |
Two ways to get the
equation of the line of best fit using MS Excel:
- Adding a Trendline to
a Scatterplot (See Curvefitting Tutorial) and
- Using the Data
Analysis Tool Pack Add-in (Shown below).
*It may help to print this page out as you work through the Excel worksheet.*
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.
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 in cell A1 and Water Consumption in cell
B1. Below the temperature, insert the values in cells A2-A8, and for
the water consumption values in cells B2-B8.
- If you need help entering the data, see the Intro to Excel
(link) tutorial.
Procedure for finding the line of best fit
- 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 by
clicking on cell B1 and holding it as you drag the box down to cell
B8.
- 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 including the title by
clicking on cell A1 and dragging the box down to cell A8.
- Press the Return to Dialog button. (The button at the end of
the field, with the small red arrow on it.)
- Under the input select the boxes for:
- Under the output options, select the button for New Worksheet Ply, and
type the word regression in the white box to the right of the New
Worksheet Ply.
- Click OK. Now the excel worksheet will be highlighted blue.
- Save your work. Select FILE > SAVE AS. Call the data set ExcelDataReg.
Learn the Procedure for calculating linear regression
equations
Simple Linear
Regression Menu Dictionary
STATS @ MTSU