Introduction to Engineering Computing GEEN 1300 Lecture 2 02 June 2010 Finish cantilever beam

1. Introduction to Engineering Computing • GEEN 1300 • Lecture 2 • 02 June 2010 • Finish cantilever beam • Graphing • Linear models • What • Why • How: the trendline

2. Cell names • Default name: column letter followed by row number • You can give cells other names via the “Name Box” • And then use those names (instead of “B1” et al.) in your formulas • Some names are reserved – watch out! • You can name a bunch of cells at once using “Create from Selection” on the “Formulas” tab

3. “Filling” Cells • Via the fill handle • Works for formulas too!!

4. Engineering calculations in Excel Deflection of cantilever beam modulus of elasticity moment of inertia

5. Deflection of cantilever beam L = 3 ft Basic data: F = 100 N E = 3 x 107 psi b = 1 in d = 2 in Find  in mm

6. Deflection of cantilever beam Find  in mm Don’t forget your name!

7. Deflection of cantilever beam Find  in mm

8. Deflection of cantilever beam • Case studies: • How does  change as L varies? • How does  change as d varies? • How does  change as b and d vary? • Lots of ways to do the one-variable case studies…

9. Using formula copy: Single formula with only one parameter changing Range of parameter values in adjacent column

10. Another example: Set up example for case study of f(x) vs x Case study by copying formula range B2:B11 named x

11. Types of graphs • X-Y graphs (“Scatter plots”) • Used most of the time • Line Graphs • Column and pie graphs • Book & lab show you how to format graphs

12. An interesting graph…

13. Same graph, formatted more nicely

14. Fitting models to data • You have some data • You have (or want to find) a candidate model for that data • Issues: • Why that model? • How to fit it to the data? • How to evaluate the fit?

15. y1 y = ax + b Model y y11 e11 x11 x The simplest model: A line For each data point, there is an error between that point and the model line. Fitting the model has to do with minimizing these errors.

16. Finding the model parameters that give the best fit For the straight-line model, the model parameters are the slope (a) and the intercept (b). The problem is then to find the values of a and b that give the best fit. What is meant by the best fit? The standard measure of goodness of fit is the sum of squares of the errors: So, the problem reduces to finding the minimum of SSE by adjusting a and b.

17. Fitting a straight-line model to data The minimization of SSE can be solved by calculus to give formulas for the best values of a and b: and Excel solves problems like this with either formulas or built-in tools (Data Analysis Regression & Trendline).

18. Example: straight-line fit

19. Transfer the data to an Excel spreadsheet and create a graph

20. Calculating the slope and intercept using Excel formulas

21. Which is the slope? • 21.32 • -41090 • 1994.5 • 1422.9

23. The formulas behind the numbers

24. Using the model straight-line equation to compute the predictions: and copy these to the graph, displaying as a straight line

25. A Shortcut: Excel’s Trendline: Start with a simple graph of the data… Select the data series by clicking on it Right-click on a data point to get context-sensitive menu Select Add Trendline option

26. The Add Trendline dialog box Linear selected by default OK for this problem Display equation on chart

27. Initial form of graph with straight line added How to assess the quality of the fit?

28. Check to display “goodness of fit” metric

29. Use this to force line to go through a particular intercept

30. If we forced this regression line to go through the origin, how would that change the R-squared value? • No change • It would be higher (better fit) • It would be lower (worse fit)

31. What are models good for?

32. Trendline can do many other things besides lines….

33. The Regression Tool in the Data Analysis “toolkit”

34. If your computer doesn’t have that toolkit: If Data Analysis does not appear on the Tools menu, you will need to check Analysis Toolpak in the Add-ins dialog box, which you get to with: …and then click the “OK” button to load it.  Excel Options  Add-ins  Go

36. Regression dialog box set up for the CO2problem checking Residuals will give us also model predictions

37. Raw regression output • Excel inserts this automatically in a new worksheet • Not very nicely formatted…

38. [ tons of info, most of which you will not understand - or need - for a couple of years ] used to judge goodness of fit intercept and slope values used to judge whether terms “belong” in the model add to data graph for visual comparison with model

39. Judging Goodness of Fit correlation coefficient: if close to +1 or –1, indicates strong correlation between x and y [something we already know from the original graph!] coefficient of determination: %-age of the variability in y that’s accounted for by the model adjustment to R2 that penalizes the value for using a model with too many terms gives an idea of how far off the model predictions will be Adjusted R2 or Standard Error can be used to compare different models and choose which fits best. The higher the value of Adjusted R2 the better, the lower the value of Standard Error the better.

40. Judging whether terms belong in the model P-values estimate the probability that the true value of the coefficient could be zero P-values that are quite small, like these, indicate that there is little question about the significance of the term coefficients. In our case here, that means that both the intercept term and the slope term belong in the model. A P-value of 5% (0.05) or greater causes suspicion that the coefficient may not be significant and that the term should probably be dropped from the model

41. Precautions on polynomial fitting Try to use the lowest-order model that gives a good fit. Higher-order models will have “wiggles” between & beyond data points that will cause prediction errors. In fact, an (n-1)th-order polynomial will provide a perfect fit to the n data points, but it will usually do bizarre things in between the data points, and outside the range where they fall.

42. The Data Analysis Regression tool is much morecomplicated and powerful than the shortcut Trendline tool. When & why should you use it? • It provides more information that lets us • judge the goodness of fit and the significance • of model terms 2) It can handle model forms that cannot be handled by Trendline So, generally, when using Excel, we prefer the Data Analysis Regression tool over Trendline but Trendline is still quite good for “quick and dirty” looks at the data Learn to use both!

43. More-complicated models… Given some data for x and y, find values for a, b, c, d, etc., that fit this model to those data Note: what we’re talking about here is called linear regression, even when there are nonlinear terms in x, because the terms are linear in the model parameters, a, b, c, etc.

44. The key to doing this in the regression tool: build these values in new columns… The data looks like this: Put in “input y range” box Put in “input x range” box

45. Which number is c? • -0.0711 • 0.000818 • -5.4 E -06 • 1.85 E -08 • -2.6 E -11

46. Precautions on polynomial fitting Try to use the lowest-order model that gives a good fit. Higher-order models will have “wiggles” between & beyond data points that will cause prediction errors. In fact, an (n-1)th-order polynomial will provide a perfect fit to the n data points, but it will usually do bizarre things in between the data points, and outside the range where they fall.

47. More complicated models… Polynomial models General linear models Examples: polynomial models above Multilinear models Examples:

48. Nonlinear models y’ a’ Transformable to linear Can use straight-line regression on this!

49. If you transform this so as to be able to use linear regression on it, given values for x and y, what’s the intercept? • a • ln a • b • ln b y = aebx

50. If you transform this so as to be able to use linear regression on it, given values for x and y, what’s the intercept? y = aebx ln(y) = ln(a) + bx ln(e) ln(y) = ln(a) + bx Intercept! “input x range” box “input y range” box