EGR 105 Foundations of Engineering I

1. EGR 105 Foundations of Engineering I Fall 2007 – week 7 Excel part 3 - regression

2. EGR105 – Week 7 Topics • Data analysis concepts • Regression methods • Function discovery by example • Regression tools in Excel • Assignment # 7

3. Analysis of x-y Data • Independent versus dependent variables y y = f(x)x dependent independent

4. 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

5. EGR105 – Week 7 Topics • Data analysis concepts • Regression methods • Function discovery by example • Regression tools in Excel • Assignment

6. EGR105 – Week 7 Topics • Data analysis concepts • Regression methods • Function discovery by example • Regression tools in Excel • Assignment

7. 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. EGR105 – Week 7 topics • Data analysis concepts • Regression methods • Function discovery by example • Regression tools in Excel • Assignment

13. Function Discoveryor How 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 (not very useful toward real problems)

14. 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

15. Experimental Setup:

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

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

18. Try a linear fit:

19. Force a zero intercept:

20. Try a quadratic polynomial:

21. Try logarithmic:

22. Try power function:

23. On log-log axes, a nice straight line:

24. EGR105 – Week 7 Topics • Data analysis concepts • Regression methods • Function discoveryby example • Regression tools in Excel • Assignment

25. 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

26. EGR105 – Week 7 Topics • Data analysis concepts • Regression methods • Function discovery by example • Regression tools in Excel • Assignment 7