Lecture11a

Lecture11a - Lecture 21 Curve Fitting using MS-Excel 1...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Lecture 21 Curve Fitting using MS-Excel 1 Generalized Least-Squares n n n n We can easily see that this process can be extended to an arbitrary degree polynomial The difficulty is that we need to solve a system of N+1 simultaneous equations to determine the coefficients of the polynomial F(x) Q: What happens if the data points are complex numbers (a+bj) ? A: We are not going to want to solve this by hand…we need an algorithm to solve a system of simultaneous complex equations 2 Example #1 Data Set X Y 14.2 25.4 36.9 50.0 62.8 72.9 83.3 94.1 104.9 X 28 31 34 37 40 43 47 50 53 Y 116.2 129.0 141.1 152.7 164.9 176.1 187.9 199.9 212.1 3 1 4 7 10 13 16 19 22 25 Example #1 Excel Datasheet X 1 4 7 10 13 16 19 22 25 28 31 34 37 40 43 47 50 53 Y 14.2 25.4 36.9 50 62.8 72.9 83.3 94.1 104.9 116.2 129 141.1 152.7 164.9 176.1 187.9 199.9 212.1 4 Example #1 Excel Graph - Linear 250 53 200 150 100 50 0 0 5 10 15 20 25 30 35 40 45 50 55 5 212.1 Example #1 – Linear Equation n y = 3.79815E+00x + 1.11825E+01 6 Example #1 Excel Datasheet - Linear X 1 4 7 10 13 16 19 22 25 28 31 34 37 40 43 47 50 53 Y 14.2 25.4 36.9 50 62.8 72.9 83.3 94.1 104.9 116.2 129 141.1 152.7 164.9 176.1 187.9 199.9 212.1 Err 0.78065 0.9751 0.86955 -0.836 -2.24155 -0.9471 0.04735 0.6418 1.23625 1.3307 -0.07485 -0.7804 -0.98595 -1.7915 -1.59705 1.79555 1.19 0.38445 TotalErr= Sqr_Err 0.609414 0.95082 0.756117 0.698896 5.024546 0.896998 0.002242 0.411907 1.528314 1.770762 0.005603 0.609024 0.972097 3.209472 2.550569 3.224 1.4161 0.147802 24.78468 7 Example #1 n n Q: Is this good enough? Is a linear function sufficient? A: It “looks” good enough, to really know we would have to increase the complexity of the model of the function and see if the total error could be reduced 8 Example #2 Excel Datasheet X 1 2 6 13 17 18 19 21 24 25 29 36 37 40 44 49 50 52 Y 14. 2 25. 4 36. 9 50 62. 8 72. 9 83. 3 94. 1 104. 9 116. 2 129 150. 2 152. 7 164. 9 176. 1 187. 9 195. 7 212. 1 9 Example #2 Excel Graph – 2nd Order X 250 1 2 6 Y 14.2 25.4 36.9 50 62.8 72.9 83.3 94.1 104.9 116.2 129 200 150 100 13 17 18 19 21 25 50 24 0 29 0 36 5 10 15 20 25 30 35 40 45 50 55 150.2 10 Example #2 – 2nd Order Equation y = -2.46142E-03x2 + 3.90230E+00x + 1.04164E+01 11 Example #2 Excel Datasheet – 2nd Order X 1 2 6 13 17 18 19 21 24 25 29 36 37 40 44 49 50 52 Y 14. 2 25. 4 36. 9 50 62. 8 72. 9 83. 3 94. 1 104. 9 116. 2 129 150. 2 152. 7 164. 9 176. 1 187. 9 195. 7 212. 1 E rr 0. 1162386 -7. 188846 -3. 158411 10. 73032 13. 24415 6. 9602999 0. 3715274 -2. 820786 -2. 246178 -9. 764488 -7. 486954 -2. 4908 -1. 268184 -2. 329872 1. 2522909 7. 8192306 3. 67785 -5. 41968 T o tal rr= E E rr_S qr 0. 0135114 51. 679502 9. 9755608 115. 13977 175. 4075 48. 445775 0. 1380326 7. 9568349 5. 0453152 95. 345216 56. 054483 6. 2040862 1. 6082906 5. 4283035 1. 5682324 61. 140367 13. 526581 29. 372928 684. 05029 12 Example #2 Excel Graph – 5th Order X 250 1 2 6 Y 14.2 25.4 36.9 50 62.8 72.9 83.3 94.1 104.9 116.2 129 200 150 100 13 17 18 19 21 25 50 24 0 29 0 36 5 10 15 20 25 30 35 40 45 50 55 150.2 13 Example #2 – 5th Order Equation y = 6.10785E-06x5 - 7.09487E-04x4 + 2.66695E-02x3 - 3.35939E-01x2 + 4.25805E+00x + 1.44970E+01 14 Example #2 Excel Datasheet – 5th Order X 1 2 6 13 17 18 19 21 24 25 29 36 37 40 44 49 50 52 Y 14. 2 25. 4 36. 9 50 62. 8 72. 9 83. 3 94. 1 104. 9 116. 2 129 150. 2 152. 7 164. 9 176. 1 187. 9 195. 7 212. 1 E rr 4. 2450771 -3. 528456 -4. 059893 3. 6749942 7. 4399424 1. 9962786 -3. 585343 -4. 383474 0. 2122581 -5. 999074 0. 3705331 4. 1546641 4. 1841649 -1. 57828 -4. 753844 1. 556999 -0. 051125 0. 1142959 T o tal rr= E E rr_S qr 18. 02068 12. 450004 16. 482727 13. 505583 55. 352742 3. 9851282 12. 854681 19. 214848 0. 0450535 35. 988891 0. 1372948 17. 261234 17. 507236 2. 4909678 22. 599033 2. 4242459 0. 0026138 0. 0130635 250. 33603 15 ...
View Full Document

Ask a homework question - tutors are online