EGR 105 Foundations of Engineering I

1. EGR 105 Foundations of Engineering I Fall 2008 – Session 4 Excel – Plotting, Curve-Fitting, Regression TexPoint fonts used in EMF. Read the TexPoint manual before you delete this box.: AA

2. EGR105 – Session 4 Topics • Review of Basic Plotting • Data Analysis Concepts • Regression Methods • Example Function Discovery • Regression Tools in Excel • Homework Assignment

3. Analysis of x-y Data • Independent versus dependent variables dependent independent

4. Simple Plotting Generate X and Y data to Plot

5. Common Types of Plots: Y=3X2 Normal log-log: log y-log x Semi-log: log x logy = log3 + 2logx y = 3x2 Straight Line on log-log Plot!

6. Finding Other Values • Interpolation • Data between known points • Regression – curve fitting • Simple representation of data • Understand workings of system • Useful for prediction • Extrapolation • Data beyond the measured range data points

7. Curve-Fitting - Regression • Useful for noisy or uncertain data • n pairs of data (xi , yi) • Choose a functional form y = f(x) • polynomial • exponential • etc. and evaluate parameters for a “close” fit

8. y (x3,y3) (x4,y4) (x1,y1) (x2,y2) e3 ei= yi – f(xi), i =1,2,…,n x What Does “Close” Mean? errors squared sum • Want a consistent rule • Common is the least squares fit (SSE):

9. y x Quality of the Fit: Notes: is the average y value 0 R2 1 closer to 1 is a “better” fit

10. Linear Regression • Functional choicey = m x + b slopeintercept • Squared errors sum to • Set m and b derivatives to zero

11. Further Regression Possibilities: • Could force intercept: y = m x + c • Other two parameter ( a and b ) fits: • Logarithmic: y = a ln x + b • Exponential: y = a e bx • Power function: y = a x b • Other polynomials with more parameters: • Parabola: y = a x2 + bx + c • Higher order: y = a xk + bxk-1 + …

12. Excel’s Regression Tool • Highlight your chart • On chart menu, select “add trendline” • Choose type: • Linear, log, polynomial, exponential, power • Set options: • Forecast = extrapolation • Select y intercept • Show R2 value on chart • Show equation on chart

13. Linear & Quartic Curve Fit Example Y X Y Better fit but does it make sense with expected behavior? X

14. Example Function DiscoveryHow to find the best relationship • Look for straight lines on log axes: àlinear on semilog x y = a ln x + b àlinear on semilog y y = ae bx àlinear on log log y = ax b • No rule for 2nd or higher order polynomial fits

15. Previous EGR105 Project Discover how a pendulum’s timing is impacted by the: • length of the string? • mass of the bob? • Take experimental data • string, weights, rulers, and watches • Analyze data and “discover” relationships

16. Experimental Setup:

17. One Team’s Results: Mass appears to have no impact, but length does

18. To determine the effect of length, first plot the data:

19. Try a linear fit:

20. Force a zero intercept:

21. Try a quadratic polynomial:

22. Try logarithmic:

23. Try power function:

24. On log-log axes, a nice straight line: b Power Law Relation:

25.     Force (lb) Collected Data    Cubic Fit Better and it Makes Sense with the Physics   Linear Fit  Elastic Bungee Cord Models Determined by Curve Fitting the Data • Linear Model (Hooke’s Law): • Nonlinear Cubic Model:

26. Homework Assignment See passed out sheet or course web site