The
Spreadsheet and Linear Regression Equation
Najia
Bao
Good morning. Today we will explore linear
regression equation by Excel.
As we know, a linear function which can be described
by an equation of the form y = ax + b, where a and b are constants. Recall that
the graph of every function of this form is a line with slope a and yintercept
b. Now letŐs solve problem 8 in this assignment.
Problem
8
Use the Cooled Data to explore
and generate a function to predict.
Suppose that we take a cup of hot water and measure its
initial temperature which is 212 ˇF (time = 0) and then record
temperature readings each minute for 30 minutes (see table 1). And it is
assumed that the room temperature is 75ˇF.
Table 1. The Cooled Data
Time (minutes) 
Temperature (ˇF) 
Time (minutes) 
Temperature (ˇF) 
0 
212 
16 
152 
1 
205 
17 
150 
2 
201 
18 
149 
3 
193 
19 
147 
4 
189 
20 
145 
5 
184 
21 
143 
6 
181 
22 
141 
7 
178 
23 
140 
8 
172 
24 
139 
9 
170 
25 
137 
10 
167 
26 
135 
11 
163 
27 
133 
12 
161 
28 
132 
13 
159 
29 
131 
14 
155 
30 
130 
15 
153 


We use the following chart (see fig. 1) to demonstrate the
data in table 1.
Figure 1
As we know, a straight line called the linear regression
line can model many data sets. Excel provides the slope and intercept functions
to calculate the slope and y intercept of the linear regression line for a set
of data pairs. LetŐs have a look at a linear model –the purple line
constructed by Excel. We can see there are two sets of points – the
scattered ones are the actual data (black) while the straight line (purple) is
the linear – regression line (see fig. 2).
Figure 2
When we know the slope and Y
intercept of a linear regression line, we can calculate predicted values of y
for any x using the formula y = ax + b. Now letŐs use the function y = 2.52x +
197.32 predict the temperature at 45^{th} minute, which is 84.12ˇF (see fig. 3).
Figure 3
We should notice that at the 60^{th}
minute, the temperature is 75ˇF (see fig 3.) but not the result of
computation which is 46.37ˇF based on the function because the
temperature decreased to the room temperature 75ˇF at the 49^{th} minute.
Similarly the temperature is still 75ˇF at the 300^{th} minute
(see fig 4.).
Figure 4
This is why whenever we write and solve word
problems, we should always take into account situations in the real world.