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 y-intercept 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 45th minute, which is 84.12ˇF (see fig. 3).

Figure 3

We should notice that at the 60th 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 49th minute. Similarly the temperature is still 75ˇF at the 300th minute (see fig 4.).

Figure 4

# Conclusion

This is why whenever we write and solve word problems, we should always take into account situations in the real world.