Computing the Sum of Squares Error (SSE)
(Based on Example 1 in the text)
Note: Make sure macros are enabled; otherwise, the buttons will not work.
Here, we compute the value of SSE using the line y = 8t + 72 to approximate the given data.
t y (Observed) y (Predicted) Residue^2 m b
-4 50 8 72 Given values of m and b
-2 57 SSE: 0
0 68 1. To compute y (Predicted), use the formula
1 72 =$E$15*A15+$F$15
2 80 representing m*t + b
3 83 in cell C15 and copy it down to C22.
90 2. To compute the residue squared, use the formula
70 representing (y Obs - y Pred)^2
60 in cell D15 and copy it down to D22.
3. Now look at the formula for SSE in cell F17.
4. Now change the values of m and b in cells E15 and F15 to see its
effect on SSE. Now try to change m and b to make SSE as small a
-6 -4 -2 0 2 4 Notice the effect on the graph: a smaller SSE means a line that
y (Observed) is closer to the data points.
Given values of m and b
ared, use the formula
SE in cell F17.
and b in cells E15 and F15 to see its
ange m and b to make SSE as small as possible.
h: a smaller SSE means a line that
Using the Regression Formula (See sheet "Built-In" for use of the built-in regression features.)
(Based on Example 2 in the book.) Note: Make sure macros are enabled; otherwise, the buttons will not work.
Example 2 Example 2* (For you to complete)
Look at the chart below with columns labeled x and y. Below are a set of x- and y-coordinates.
The x- and y-columns contain the data from the example in the book. You are to complete the regression by filling in the appropriate form
Look at the formulas in the xy- and x_squared columns, and compare
them with the formulas in the text. Press here to see the correct equation:
We have placed the cells containing the results above the table
in case we wish to add additional data points.
Cells C28 and E28 contain the formulas for m and b. Equation: y = x + 0
Experiment by changing the calues of x and y in the table. n Sums:
If you wish to add an additional data point, add the new
x- and y-values, and copy the remaining formulas in the x y
new row (xy and x_squared). 160 126
Results: m= 0.5 b= 0.8 220 75
Equation: y = 0.5x + 0.8 240 82
4 10 8.2 23 30
x y xy x_squared
1 1.5 1.5 1
2 1.6 3.2 4
3 2.1 6.3 9
4 3 12 16
e buttons will not work.
(For you to complete)
ession by filling in the appropriate formulas.
Using the built-in linear regression functions
(Based on Example 3 in the book.)
x y Using the built-in trendline feature
-3 55 1. Highlight the table of x- and y-values (cells A7 through B15)
-2 57 2. Create an XY (Scatter) graph with points (no lines) and "series in columns."
-1 60 3. With the chart selected, select Chart -> Add Trendline… and choose the "linear"
0 68 trendline. Under "Options," choose "Display equation."
"series in columns."
… and choose the "linear"