Week 14 Spreadsheets for Engineering Applications part 2 BJ Furman 26APR2010 The Plan for Today Solver Review Non linear regression analysis Macros and VBA programming Learning Objectives Use Solver to solve mathematical equations Apply Solver to perform regression linear and non linear Explain the utility of Visual Basic for Applications VBA Create a VBA macro to automate tasks Excel Solver Add in tool comprised of optimization algorithms Goal minimize or maximize an objective function subject to constraints by changing a set of parameters that the objective function depends upon Can be used to find roots solve simultaneous equations solve problems requiring iteration etc Activating Solver Excel 2003 Make sure Solver Add in is checked Activating Solver Excel 2007 Office Button Solving Simultaneous Equations Find the currents i1 i2 i3 i3 Kirchhoff s Current Law at A i3 R1 V i2 R2 i2 R3 V Kirchoff s Voltage Law across R1 and R3 R3 V R2 R1 Circuit analysis EE 98 i2 i1 i1 i2 i3 0 0i1 0i2 i3 R1 V 0i1 R2 R3 i2 0i3 V Equations in matrix form 1 0 0 1 0 R2 R3 1 i1 0 R1 i2 V 0 i3 V Could solve by matrix inversion i A 1 V but let s use the Solver instead Steps for Solving Simultaneous Equations With Solver Steps 1 Define constants and variables Put guesses in for the variables 2 Express m equations in n variables and constants as fi xj 0 where i 1 to m and j 1 to n 3 Form the equation y fi2 4 Use the Solver and change the xj values to drive y the Target Cell to be zero Example Excel pt2 xls Circuit Analysis Example with Solver 1 Define constants and variables Add guesses for variables Note named ranges 2 Express equations in the variables and constants 3 Form the equations y fi2 4 Solver and change the variables to drive y the Target Cell to 0 Adding Names to Ranges Highlight names and values 2003 Insert Name Create Left column 2007 Formulas Defined Names tab Create from Selection Left column Result Back Regression Analysis Coined by biologist Francis Galton 1822 1911 Searching for a mathematical law describing the tendency of offspring s characteristics to revert regress back to the average of its ancestors Statistical method to investigate the relationship between dependent and independent variables Fit a mathematical model to a set of data Ex best fit straight line trendline through data points from a phenomena that is thought to be of the form y mx b Find the coefficients of the model equation that minimizes the sum of squared error SSE between the actual dependent variable values and those predicted by the model Linear Regression Linear regression Where the coefficients of the model are linear Least Squares Curve Fit 14 y 1 1693x 12 2 R 0 9538 10 8 Y 6 4 Examples y mx b y a b log x y a bX cX2 error 2 0 0 2 4 6 X 8 10 12 Non linear Regression Non linear regression Where the coefficients of the model are non linear Example y Ae t C Which coefficient is non linear 120 error 100 80 Temperature C Temperature Response 60 40 20 0 0 2 4 6 Time Sec 8 10 12 Regression Analysis Procedure Steps 1 2 3 4 5 6 7 8 Select a regression model e g y mx b Enter the data set dependent and independent variable values and initial guesses for the regression model coefficients Calculate the predicted dependent values using the regression model and the independent variable s Calculate the error values actual predicted Calculate the squared errors Calculate the sum of the squared errors SSE Use Solver to minimize select Min the SSE Target Cell by changing the value of the coefficients m and b Test your result by 1 calculating the coefficient of determination R2 2 plotting your model curve to the data 3 plotting Ypred vs Yi and 4 plotting the deviations Yi Ypred R2 1 SSE where TSS is the Total Sum of Squares TSS y y where y is the average of the actual dependent variable in the data set N TSS 2 i 1 and the yi are the values of the actual dependent variable Non linear Regression Example Thermal step response Expose an object at uniform temperature to a step change in surrounding temperature Ex Lobster put into a pot of boiling water thermocouple at the center of the lobster measure lobster temperature as function of time Use the data to determine parameters of a first order model Heat in change in internal energy Assumed Solution T t T T0 T e t Example Excel pt2 xls hA T T mC dT T T dt dT dt where mC hA Spreadsheet for Non linear Regression Model curve Example Excel pt2 xls Plot of Ypred vs Yi A good fit 120 Data close to the diagonal line Yi vs Yi 100 but will depend on scatter in the data 80 Ypred C No discernable pattern in the data points around the diagonal axis Ypred vs Yi 60 40 Yi vs Yi 20 0 0 20 40 60 Measured Yi C 80 100 120 Deviation Plot A good fit Deviation Plot Yi Ypred small but will depend on scatter in the data 5 No discernable pattern in the data points around the horizontal axis 3 4 2 Yi Ypred 1 0 1 0 2 4 6 2 3 4 5 6 Time Sec 8 10 12 Excel Macros and VBA Macro a set of recorded key strokes or program written in Visual Basic VB to automate tasks Visual Basic for Applications VBA An implementation of VB integrated into MS Office applications Enables user to write VB code to automate tasks and much more Security and Working With Macros Be careful with macro enabled files Excel 2007 xlsx macro dis abled workbooks default xlsm macro enabled workbooks You can write and work with a macro in a xlsx file but you can only save the macro to a xlsm file You may need to modify Macro Settings Office button Excel Options Trust Center Settings Macro Settings Disable all macros with notification Excel 2003 Set security level to medium Accessing Macros in Excel 2003 Tools Macro Accessing Macros in Excel 2007 View Macro Recording Macros Excel 2007 View Macros Macros menu Record Macro Excel 2003 Tools Macro Record New Macro Ex lbf to N conversion lbf to N Conversion Macro Make sure that relative addressing is selected VB Editor Review References Larsen R W 2009 Engineering with Excel Pearson Prentice Hall New Jersey ISBN 0 13 601775 4 Engineering with Excel companion website http www chbe montana edu excel EngExcel3 htm Visited 25OCT2009 First Order System Transient Response of a Thermocouple to a Step Temperature Change Available on line http www colorado edu MCEN Measlab background1st order pdf Visted 24APR2010