Using the 8 non-shaded values above, find a_0 and a_1 for the least squares line
ID: 3677690 • Letter: U
Question
Using the 8 non-shaded values above, find a_0 and a_1 for the least squares linear regression. We will save the shaded values for our test data, that is, data points that are known but we will not include in the information used to make a representative curve. We will use these points to sec how close our curve fit is to predicting actual values that were not used to derive the curve. Compute the overall squared-error. Write the completed polynomial. Using the 8 non-shaded values from part A, find a_0, a_1, and a_2 for a parabolic least squares regression (polynomial of degree 2). Use MS Excel to solve for these coefficients. Compute the overall squared-error. Write the completed polynomial. Include a printout of your Excel spreadsheet. On two separate graphs, plot the non-shaded data points and show the resulting curves from Part A and Part B; a separate graph for each curve. Use graph paper. Fill in the following test table: which method(s) performed the best? Would you have expected the outcomes? How do these perform for these data points vs. the linear and parabolic curve's squared errors? Discuss your answer.Explanation / Answer
PART-A
n = 8 ( x) 2 = 46225
x = 215 mean of x = 26.875
y = 277 mean of y = 34.625
x y = 59555 x y = 8505
x2 = 7625
a1 = n xy - x y
n x2 - ( x) 2
a1 = 8(8505) – (215)(277)
8(7625) – 46225
a1 = 0.5743
a0 = mean of y – (a1)(mean of x)
a0 = 34.625 – (0.5743)(26.875)
a0 = 19.1912
Least squares linear regression:
Y = 19.1912 + 0.5743X
Overall Squared Error = e 2 = (y – ao – a1x)2
= (yactual – y model)2
= (17 – 19.1912 – 0.5743 (5))2 + ...+ (46 – 19.1912 – 0.5743(50))2
Overall Squared Error = 60.7783
PART-B:-
MS Excel worksheet
8
215
7625
215
7625
306125
M
7625
306125
13113125
1.41605314
-0.1094736
0.00173225
-0.1094736
0.01055302
-0.0001827
M-1
0.00173225
-0.0001827
3.3342E-06
277
8505
N
318825
13.4580689
1.17896251
M-1 x N
-0.0110349
Least squares parabolic regression:
Y = 13.4581 + 1.1790X – 0.0110X2
Overall Squared Error = e 2 = (y – ao – a1x – a2x2)2
= (yactual – y model)2
= (17 – 13.4581 – 1.1790(5) + 0.0110(5)2)2 + . . . + (46 –13.4581 – 1.1790(50) + 0.0110(50)2)2
Overall Squared Error = 24.2566
13.4580689
1.17896251
M-1 x N
-0.0110349
Least squares parabolic regression:
Y = 13.4581 + 1.1790X – 0.0110X2
Overall Squared Error = e 2 = (y – ao – a1x – a2x2)2
= (yactual – y model)2
= (17 – 13.4581 – 1.1790(5) + 0.0110(5)2)2 + . . . + (46 –13.4581 – 1.1790(50) + 0.0110(50)2)2
Overall Squared Error = 24.2566
MS Excel worksheet
8
215
7625
215
7625
306125
M
7625
306125
13113125
1.41605314
-0.1094736
0.00173225
-0.1094736
0.01055302
-0.0001827
M-1
0.00173225
-0.0001827
3.3342E-06
277
8505
N
318825
13.4580689
1.17896251
M-1 x N
-0.0110349
Least squares parabolic regression:
Y = 13.4581 + 1.1790X – 0.0110X2
Overall Squared Error = e 2 = (y – ao – a1x – a2x2)2
= (yactual – y model)2
= (17 – 13.4581 – 1.1790(5) + 0.0110(5)2)2 + . . . + (46 –13.4581 – 1.1790(50) + 0.0110(50)2)2
Overall Squared Error = 24.2566
MS Excel worksheet
8
215
7625
215
7625
306125
M
7625
306125
13113125
1.41605314
-0.1094736
0.00173225
-0.1094736
0.01055302
-0.0001827
M-1
0.00173225
-0.0001827
3.3342E-06
277
8505
N
318825
13.4580689
1.17896251
M-1 x N
-0.0110349
Least squares parabolic regression:
Y = 13.4581 + 1.1790X – 0.0110X2
Overall Squared Error = e 2 = (y – ao – a1x – a2x2)2
= (yactual – y model)2
= (17 – 13.4581 – 1.1790(5) + 0.0110(5)2)2 + . . . + (46 –13.4581 – 1.1790(50) + 0.0110(50)2)2
Overall Squared Error = 24.2566
MS Excel worksheet
8
215
7625
215
7625
306125
M
7625
306125
13113125
1.41605314
-0.1094736
0.00173225
-0.1094736
0.01055302
-0.0001827
M-1
0.00173225
-0.0001827
3.3342E-06
277
8505
N
318825
13.4580689
1.17896251
M-1 x N
-0.0110349
Least squares parabolic regression:
Y = 13.4581 + 1.1790X – 0.0110X2
Overall Squared Error = e 2 = (y – ao – a1x – a2x2)2
= (yactual – y model)2
= (17 – 13.4581 – 1.1790(5) + 0.0110(5)2)2 + . . . + (46 –13.4581 – 1.1790(50) + 0.0110(50)2)2
Overall Squared Error = 24.2566
MS Excel worksheet
8
215
7625
215
7625
306125
M
7625
306125
13113125
1.41605314
-0.1094736
0.00173225
-0.1094736
0.01055302
-0.0001827
M-1
0.00173225
-0.0001827
3.3342E-06
277
8505
N
318825
13.4580689
1.17896251
M-1 x N
-0.0110349
Least squares parabolic regression:
Y = 13.4581 + 1.1790X – 0.0110X2
Overall Squared Error = e 2 = (y – ao – a1x – a2x2)2
= (yactual – y model)2
= (17 – 13.4581 – 1.1790(5) + 0.0110(5)2)2 + . . . + (46 –13.4581 – 1.1790(50) + 0.0110(50)2)2
Overall Squared Error = 24.2566
MS Excel worksheet
8
215
7625
215
7625
306125
M
7625
306125
13113125
1.41605314
-0.1094736
0.00173225
-0.1094736
0.01055302
-0.0001827
M-1
0.00173225
-0.0001827
3.3342E-06
277
8505
N
318825
13.4580689
1.17896251
M-1 x N
-0.0110349
Least squares parabolic regression:
Y = 13.4581 + 1.1790X – 0.0110X2
Overall Squared Error = e 2 = (y – ao – a1x – a2x2)2
= (yactual – y model)2
= (17 – 13.4581 – 1.1790(5) + 0.0110(5)2)2 + . . . + (46 –13.4581 – 1.1790(50) + 0.0110(50)2)2
Overall Squared Error = 24.2566
MS Excel worksheet
8
215
7625
215
7625
306125
M
7625
306125
13113125
1.41605314
-0.1094736
0.00173225
-0.1094736
0.01055302
-0.0001827
M-1
0.00173225
-0.0001827
3.3342E-06
277
8505
N
318825
13.4580689
1.17896251
M-1 x N
-0.0110349
Least squares parabolic regression:
Y = 13.4581 + 1.1790X – 0.0110X2
Overall Squared Error = e 2 = (y – ao – a1x – a2x2)2
= (yactual – y model)2
= (17 – 13.4581 – 1.1790(5) + 0.0110(5)2)2 + . . . + (46 –13.4581 – 1.1790(50) + 0.0110(50)2)2
Overall Squared Error = 24.2566
MS Excel worksheet
8
215
7625
215
7625
306125
M
7625
306125
13113125
1.41605314
-0.1094736
0.00173225
-0.1094736
0.01055302
-0.0001827
M-1
0.00173225
-0.0001827
3.3342E-06
277
8505
N
318825
13.4580689
1.17896251
M-1 x N
-0.0110349
Least squares parabolic regression:
Y = 13.4581 + 1.1790X – 0.0110X2
Overall Squared Error = e 2 = (y – ao – a1x – a2x2)2
= (yactual – y model)2
= (17 – 13.4581 – 1.1790(5) + 0.0110(5)2)2 + . . . + (46 –13.4581 – 1.1790(50) + 0.0110(50)2)2
Overall Squared Error = 24.2566
PART-D:-
Y values
Absolute Error:
|Actual - Predicted|
Results
X
linear interpolation
linear fit
parabolic fit
linear interpolation
linear fit
Parabolic fit
actual
best value
best method
24
38.5
34.68
36.64
3.5
4.32
2.36
37
36.64
Parabolic
44
45.5
46.16
47.02
2.5
2.16
2.02
44
46.16
Linear
The parabolic fit (regression) was the best method for (X = 24). The linear fit was the best method for (X = 44). Based on the graph which appeared to provide a better ‘fit’ to the data and overall squared error, which was less for the parabolic regression, the expectation would be that the parabolic regression would have the best fit to the actual data. But that was only true for the one set of data (X = 24). Perhaps other ways to obtain or access the error, besides absolute error (which just took the difference), are needed to determine which regression provides the best fit for all the data.
Part-E:-
Private Sub GoExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GoExcel.Click
'------------------------------------------------------
'The Setup - always the same
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
oXL = CreateObject("Excel.Application")
oXL.Visible = True 'this makes it so they can see what's happening
oWB = oXL.Workbooks.Add
oSheet = oWB.ActiveSheet
'-------------------------------------------------------
'SOLVING MX=N
'now you have a sheet open
'ENTER MATRIX M
oSheet.Cells(2, "A") = "=8" 'row, column -- refers to cell A2
oSheet.Cells(3, "A") = "=215" 'row, column -- refers to cell A3
oSheet.Cells(4, "A") = "=7625" 'row, column -- refers to cell A4
oSheet.Cells(2, "B") = "=215" 'row, column -- refers to cell B2
oSheet.Cells(3, "B") = "=7625" 'row, column -- refers to cell B3
oSheet.Cells(4, "B") = "=306125" 'row, column -- refers to cell B4
oSheet.Cells(2, "C") = "=7625" 'row, column -- refers to cell C2
oSheet.Cells(3, "C") = "=306125" 'row, column -- refers to cell C3
oSheet.Cells(4, "C") = "=13113125" 'row, column -- refers to cell C4
'matrix has been entered in A2..C4
'ENTER MATRIX N
oSheet.Cells(2, "E") = "=277"
oSheet.Cells(3, "E") = "=8505"
oSheet.Cells(4, "E") = "=318825"
'matrix has been entered in E2..E4
'now put the inverse command in a cell
'Since this has to be entered as an array formula
'follow the directions in MS Excel
'1. Put the formula for the inverse =MINVERSE(A2:C4) in the upper
‘left cell A6
'2. Select a range (A6:C8) in this example
'3. Press F2
'4. Press CTRL+SHIFT+ENTER
'1
oSheet.Cells(6, "A") = "=MINVERSE(A2:C4)"
'2
oSheet.Range("A6", "C8").Select()
'3 - the true tells VB .NET to wait until the keys have been
‘processed by MS Excel before moving on
oXL.SendKeys("{F2}", True)
'4: CTRL is ^ (carrot)
' SHIFT is + (plus)
' ENTER is {ENTER}
oXL.SendKeys("^+{ENTER}", True)
'the inverse is there in A6:C8
'To solve MX = N, take X=(Minverse)N
'so Matrix is A6:C8 times E2:E4 and put answer in A10:A12
'This is also an array operation just like above
'so we must follow steps 1 - 4 for MMULT
'1
oSheet.Cells("10", "A") = "=MMULT(A6:C8, E2:E4)"
'2
oSheet.Range("A10", "A12").Select()
'3 - the true tells VB .NET to wait until the keys have been
‘processed by MS Excel before moving on
oXL.SendKeys("{F2}", True)
'4: CTRL is ^ (carrot)
' SHIFT is + (plus)
' ENTER is {ENTER}
oXL.SendKeys("^+{ENTER}", True)
'the solution matrix X is now in A10:A12
'now save the Excel File
'NOTE THAT .VISIBLE must be true since it uses SENDKEYS
'othewise they could normally use false to do it in the background
oWB.SaveAs("F:CSES141.xls")
oXL.Quit()
End Sub
Part-C:-Take the graph paper and plot the values it is very easy.
NOTE:-The above values are approximate values so please calculate once again the values according to the given formulas but the procedure is same.
8
215
7625
215
7625
306125
M
7625
306125
13113125
1.41605314
-0.1094736
0.00173225
-0.1094736
0.01055302
-0.0001827
M-1
0.00173225
-0.0001827
3.3342E-06
277
8505
N
318825
13.4580689
1.17896251
M-1 x N
-0.0110349
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.