ࡱ;   !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJLMVOPQRSTUKWXRoot EntryFA,Book CompObj^SummaryInformation(Np FMicrosoft Excel 5.0 WorksheetBiff5Excel.Sheet.5;  Oh+'Oh+'0@ P08G#0     [ ``iݜ*o?H6A^BbzbO/(fvEE>j(9jJ2 """)))000___UUUMMMBBB999 ```( +9%I;/]E:IS+!!lYQGj2ga1aS {Cg..&YQFh.IRj#SjJu3lJA7e ,N, QdoV YC6r3w_GqC-}zn#&s[ Rp$L 6n{DuqVJ4H2Aph6B"}3%6Z\H"BMXR ӕ$sVo<gX %&w!'/N(/),Ч-/02446 8 9; => (2,3) '!Displacements of the red points! (3,3)+"#from the blue points along the line#y=mx+b.# (2,2m+b)+%#Displacement is a vertical measure.%(1,m+b))&! --differences in y-coordinates.' d_1: |m+b-1|(d_2: |2m+b-3| ((1,1))d_3: |3m+b-3|, MINIMIZE:j-bthe sum of the squares of the displacements so that the absolute value is no problem to deal with.x/pThe line that "best" fits the data will be the one that is least far from the data points--the one for which the?07 sums of the squares of the displacements is a minimum.2 MINIMIZE:44,f(m,b) = (m+b-1)^2 + (2m+b-3)^2 + (3m+b-3)^26 SIMPLIFY:#8f(m,b) = 14m^2 + 12mb - 32m)9! +3b^2 - 14b + 19z;rTo find the minimum, find suitable partial derivatives, set them equal to zero, and solve to find critical points.7=/partial of f with respect to m = 28m + 12b - 329>1partial of f with respect to b = 12m + 6b - 14.=/(B-+n|C8'-~;@B  \ sandy B=y,8X@"1Arial1Arial1Arial1Arial1Arial1Arial1Arial1Arial1Arial"$"#,##0_);\("$"#,##0\)"$"#,##0_);[Red]\("$"#,##0\) "$"#,##0.00_);\("$"#,##0.00\)%""$"#,##0.00_);[Red]\("$"#,##0.00\)5*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_),))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)=,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)4+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)                + ) , *   !  @ #   !  p  p "x @&x @ Sheet1 |Sheet2 }Sheet3 ~Sheet4 ySheet5 qSheet6 iSheet7 aSheet8 YSheet9QSheet10ISheet11ASheet129Sheet131Sheet14)Sheet15!Sheet16   8`r'i+k17>ZGNU  dMbP?_*+%&APage &PMHP LaserJet III 8DR "d??U ` bww! /N /  Ч4   l=  ;-l NRE 545 Curve Fitting Handout #1 S. Arlinghaus!Polynomials--general formIAy=a_nx^n + b_(n-1)x^(n-1) +...+ a_3x^3 + a_2x^2 + a_1x^1 + a_0x^0QIThe simplest form of a polynomial is a straight line, generated either as   y=a_0x^0=a constant orw oy=a_1x^1 + a_0x^0=mx+b where m is the slope of the line and (0,b) is the intercept of the line with the y-axis.>61. Simple fitting of unbounded curves using Excel 5.0zrGiven a finite set of data points in two dimensions. Find a straight line that "best" fits the scatter of points._WOne might minimize the absolute value of the displacements of the points from the line;OGone might minimize the squares of the absolute values of displacements;;3or, one might minimize a variety of other measures.bZThe "regression" analysis featured in most spreadsheets is a "least squares" analysis thatxpminimizes the squares of the absolute values of displacements of the actual data points from the line to be fit..&Consider the following simple example:#Data points--filled in red: (1,1) (2,3) (3,3)  displacements from line(points filled in blue)(3,3m+b)6J$%MU {B~cS?f|2' # !"#bwDEbwGIw!K/NM/NOЧPQSUV4W Y Z[ \]^l= _ j@bSet the two partial derivatives equal to zero and solve the set of two equations in two variables:BSolve:D 14m + 6b =16E 12m + 6b =14GTherefore, m=1; b = 1/3AG9a minimum (in theory could be a maximum or saddle point).2I*So, y=mx+b becomes, for these data points:VKNy=x+1/3, the line of best fit obtained by minimizing squares of displacements.iMaThis very simple example illustrates WHY the strategy works; it also illustrates that the role ofyNqoutliers is perhaps overly emphasized--a data point that is 5 units from the line of best fit contributes a valueqOiof 25. Thus, in some cases, one might want to consider lines minimizing strict linear displacement, evenvPnthough it is more trouble to deal with and is not customarily treated in "black-box" mode, as is least squaresQ analysis.,S$BLACK-BOX treatment of least squaresjUbMost spreadsheets offer a very easy strategy for dealing with least squares "regression" analysis.qViOf course, the manner of handling the analysis often changes, from one update to the next, so having some8W0understanding of what is happening is important.LYDUse Excel 5.0 to find the line of best fit to the data points above.Z Data points: [x [y\??]@@^@@[_SEnter these by hand, or import them and set them up in a manner such as that above.2nh6Zm}uz0nu<Pa c d e bwf g w!h /Ni /k n Чo qrst4u v wx yzVaNThen, use the commands SLOPE and INTERCEPT to find the values to put in y=mx+bdc\Type =SLOPE({1,3,3},{1,2,3}) to return the value of the slope of the line of least squares--bdZ note that the numbers in the first set are the y's and in the second set are the x's.nefType =INTERCEPT({1,3,3},{1,2,3}) to return the value of the y-intercept of the line of least squares--bfZ note that the numbers in the first set are the y's and in the second set are the x's. g hSLOPE CALCULATION:eh ?LCU`dwP`dwPA;?@@?@@ i kINTERCEPT CALCULATION:ek XUUUUU?h ` ewP`(ewPA7?@@?@@kncThen, it is an easy matter to read off the equation of the line of least squares as y=1*x+0.33333334o,The equation can be used to forecast values. qx qyqy=mx+br??$rRTU?zDrF]kSU?7r/Enter the extended set of x-values--4,5,6,7,8,9s@@sk}@rsszLF]kSU?t@@tk} @ssRtJThen, in the y-column, next to the 4, type =a118+0.333333 (that is, mx+b).~ u@$uLuTU@k Du1ZGU?uԵ>UU@ts[uSThen, copy the cell b118 and paste it into the highlighted range from b119 to b123.~ v@vLuTU@uvvzL1ZGU?vԵ>UU@us~ w@wLuTU@vvwԵ>UU@vsPwHYou can use chartWizard to graph the results. In many installations of ~ x@xLuTU@wvxԵ>UU@wsFx>Excel, you can do all this at once using tools, data analysis.~ y @y&: @xvyZ{ @xs~ z"@z&:"@yvzZ{"@ys. Zhfrf o8,yQhLbww!/N/Ч4,$2. Simple non-linear curve fitting.)!Exponential functions in general:"y=a^x, a is a fixed number Suppose a>1 E= An increase in x causes an eventually sharper increase in y."Consider y=2^x for example x y???@@@ (0,1) Suppose 00 and that a is not 1.A9Then, one of the two familiar exponential curves emerges.0'I L+ytnpbww!/N/Ч4  1)3. Least squares and exponential curves.Basic definition:+#Log_a y = x if and only if a^x = y.f^That is, exponential and logarithmic function are inverses--one can be used to undo the other.C;Consider the same data set as before: (1,1), (2,3), (3,3).MESuppose that we can fit a line of the form ln y = mx + b to the data.A9Then, from this line, we get (using the basic definition) y=e^(mx+b)*"as an exponential fit to the data.0(To do this, take the ln of the y values:( (1, ln 1), (2, ln 3), (3, ln 3).=5Now, fit a line to these points, using least squares. x ln y y~ ?zDA~ ?~ @ z?LA~ @~ @ z?~ @Slope:e/-?=`dwP`|ewPA;/-?/-??@@ Intercept:eLo׿`ewP`ewPA7/-?/-??@@(T5/jGQE.4,A*=P;{bww!/N/Ч4   %Thus, ln y = 0.549306x-0.3662%So, y = e^(0.549306x-0.3662) tlSo, the exponential fit to the given values of x=1,2,3 is given below, as is the projected fit for x=4,...,9actual y y=x+0.333 xy=e^(0.549306*x-0.3662)~ ?1l-7?/-?Do?A~ ?$|a2U?DioT?~ @W#@%/-?Lo?A~ @ 0@LioT?~ @-l @~ @ 0 @~ @{>< @_LU@~ @Aq%@_LU@~ @R2@_LU@~ @?os6@@_LU@~  @rftL@{/L @~ "@QX@{/L"@1)4. Least squares and logarithmic curves.Basic definition:+#Log_a y = x if and only if a^x = y.f^That is, exponential and logarithmic function are inverses--one can be used to undo the other.C;Consider the same data set as before: (1,1), (2,3), (3,3).* h))x)0yZLLLLLL5/jbw w! /N/Ч4   MESuppose that we can fit a line of the form e^ y = mx + b to the data.A9Then, from this line, we get (using the basic definition) y=ln(mx+b)/'as a logarithmic curve fit to the data.1)To do this, take the exp of the y values:(  (1, e^ 1), (2, e^ 3), (3, e^ 3).= 5Now, fit a line to these points, using least squares. x e ^y y~ ?iW @&DA~ ?~ @o4@LA~ @~ @o4@~ @Slope:e1˞^!@`ewP`ewPA;q @4@4@?@@ Intercept:eTh`H wP`f wPA7q @4@4@?@@%Thus, e^ y = 8.683629x-3.0708%So, y = ln(8.683629x-3.0708) zrSo, the logarithmic curve fit to the given values of x=1,2,3 is given below, as is the projected fit for x=4,...,9actual y y=x+0.333 xy=e^(0.549306*x-0.3662)y=ln(8.683629*x-3.0708) exponentiallinear (actual)linear (proj.) logarithmic~ ?1l-7?/-?Do?A~ ?$|a2U?DioT?1"ә?1˞^!@DTt$@A~ @W#@%&/-?Lo?A~ @ 0@&LioT?[*FG@%&1˞^!@LTt$@A.UQE35,A*=P;{))~)Sc !"#bw$%w!&/N-/.0Ч478:;4< = ~ @ -l @~ @  0 @  - @~ !@!{>< @ !_LU@!!0 @ ~ "@"Aq%@!"_LU@""ZL @!~ #@#R2@"#_LU@##n#@"~ $@$?os6@@#$_LU@$$01<8@#~ % @%rftL@$%{/L @%%@]@$~ &"@&QX@%&{/L"@&&9F@%X-PAll of linear, exponential, and logarithmic curves offer a reasonable fit to theY.Qactual data; however, the various futures forecast by each are vastly different!!N0FNote diagrams of this sort in Meadows--this is the theory behind them. 4 W7OIf the tools, data analysis feature is installed, least squares is easy to do; R8Jadditional information is produced, and graphs can be drawn directly, too. :x :e ^y :y~ ;?;iW @&D;A~ ;?~ <@<o4@;<<=LA~ <@~ =@=o4@<<~ =@& @ykkkkkk\]R [V*=PEF GHbwIJw!K/NL/ N OЧP Q R $S "T 4U  V  &Z [  &\ ] ^ l= _   ESUMMARY OUTPUTGRegression StatisticsGH Multiple RHLXz?IR Square~ IR@JAdjusted R Square~ JI@KStandard ErrorK!P\@L Observations~ L@ NANOVAO Odf OSS OMS OFOSignificance FP Regression~ P?PrEb@PrEb@~ Pr@P9TUU?QResidual~ Q?Q걍җ"I@Q걍җ"I@ Q RTotal~ R@R챍җ"i@ RTT CoefficientsTStandard ErrorTt StatTP-valueT Lower 95%T Upper 95%T Lower 95.000%T Upper 95.000%U InterceptUEUɁg(%@Us%ҿUR_?UDEݕaU}ϧU`@UDEݕaU}ϧU`@V X Variable 1V& n^!@VC @VLXz?V9TUU?V}q%aKVE <R@V}q%aKVE <R@ZRESIDUAL OUTPUT\ Observation\ Predicted Y\ Residuals~ ]?]9s@](~ ^@^֊ȗ,@^"(@~ _@_ q|6@_((2?+("+,&[hTAC22]*+x@]** *@]*@ @*@], &@'< @],  P!K @],  P!Z @]*!`!@]*$p$@]* ''@]* )*Z@]* )*Z@]* )*K@], %P&- @],"P#i @], 0 @]>{- @A@D wPSrWwP  &APage &P"{??3?3DFA3O343*43" 444 x ]>{- @A@ wPSrWwP  &APage &P"{??3??3 3QQ;`W rzQ;`W rzE4 3QQ;`W rzQ;rzE4D FA  3O 3*43*4523  43"  3O %3OQ44$%3O&Q43      4444 D           ]*  @]* @], `KA@@]*-@]*@], 0<0A@@]*!K-@]*"0 @]*#0 @]*$@]*%p 0@]*& `@],*0- @],+PK @], ,PK @],!-@- @],".@- @]>#/ @@A@wPSrWwP  &APage &PMHP LaserJet III 8DR "d??3 3 3QQ;`W Q;aW E4 3QQ;1aW Q;MaW E4 3QQ;iaW Q;E4D FA' W 3Ot z3*43*4523  43"  cI3O %3OQ44$%3O&Q4444 N                ]>$00@2@A@wP.TrWwP  &APage &PMHP LaserJet III 8DR "d??3$3 3Q:pW   exponentialQ; qW &Q;)qW &E4 3Q:AqW  linear (actual)Q;]qW &Q;yqW &E4 3Q:qW  linear (proj.)Q;qW &Q;qW &E4 3Q:qW   logarithmicQ;qW &Q;&E4D FA.{ H 3ONh 3*43*4523  43" G 9 3OH %3OQ44$%3O&Q4444 d\ ]>%1 EO@A@wP^TrWwP  &APage &P"E??3п 33Q YQ;ipW ;=Q;pW ;=E43Q  Predicted YQ;pW ]_Q;;=E4D FA 3Ovq \3*43*4%J 3OD&Q  X Variable 1'4% 3O &Q Y'4523  43" 5 n3O6 r%3OQ44$%3O&Q43      444%UI13O& Q X Variable 1 Line Fit Plot'44 ,l ]*&2  0x@]\'3< P i @rTrActual%]`(4 <  i @ rTrProjected7 > ,????mqq         P}  dMbP?_*+%&APage &P"D??U >     H~  dMbP?_*+%&APage &P"??U >     @  dMbP?_*+%&APage &P"??U >     8  dMbP?_*+%&APage &P"??U >     0  dMbP?_*+%&APage &P"??U >     (  dMbP?_*+%&APage &P"??U >      dMbP?_*+%&APage &P"??U >       dMbP?_*+%&APage &P"??U >       dMbP?_*+%&APage &P"??U >       dMbP?_*+%&APage &P"??U >      dMbP?_*+%&APage &P"??U >      dMbP?_*+%&APage &P"??U >      dMbP?_*+%&APage &P"??U >      dMbP?_*+%&APage &P"??U >      dMbP?_*+%&APage &P"??U >  ࡱ; y7 2 8 2 92 102 11 2 6ZA 2 v\B 2 C 2 D 2 7ZE"System-'- [Arial-  2 NRE 545 -   !#Y !#[ !# !# +2 #Curve Fitting Handout #1    !4Y !4[ 2 4 S. Arlinghaus    !VY !V[ !V !V -2 VPolynomials--general form     !xY !x[ !x !x !x !x !x !x !xY !x[ !x !x i2 xAy=a_nx^n + b_(n-1)x^(n-1) +...+ a_3x^3 + a_2x^2 + a_1x^1 + a_0x^0  !Y ![ ! ! Oh+'Oh+'0@ P08G#0     [ ``iݜ*o?H6A^BbzbO/(fvEE>j(9jJ2 """)))000___UUUMMMBBB999 ```( +9%I;/]E:IS+!!lYQGj2ga1aS {Cg..&YQFh.IRj#SjJu3lJA7e ,N, QdoV YC6r3w_GqC-}zn#&s[ Rp$L 6n{DuqVJ4H2Aph6B"}3%6Z\H"BMXR ӕ$sVo<gX .YLg:}ߚ=VCq8SeFMjLZc"LeN?Pppiiiwwwfxю7Tvpxd?2}Dx#$_`,19م>w؁V!0ȳypQit|45'  ' [- -- !-""- !"-33- !3-DD- !D-UU- !U-ff- !f-ww- !w-- !-- !-- !-- !-- !-[- ![-ZZ- !Z-- !-- !-- !-ZZ- !Z-'- [-  -""[- !@"-33[- !@3-DD[- !@D-UU[- !@U-ff[- !@f-ww[- !@w-[- !@-[- !@-[- !@-[- !@-[- !@-ZZ- !Z-- !-- !-- !-ZZ- !Z-'- [ArialӮ-   2  - -- !-- !-- !-- !--  2  "1 2 # $32 2 4 5D3 2 E FU4 2 V Wf5 2 g hw6 2 x  ! ! ! ! !Y ![ ! ! u2 IThe simplest form of a polynomial is a straight line, generated either as    2    !Y ![--'- -    $2 y=a_0x^0=a constant--'- -  [- [-'-  [-  -[- ![-- !-'UUsandyhMicrosoft Excelࡱ;