excel modeling non linear regression anchored by: renu rao kaveh saba
TRANSCRIPT
Excel ModelingNon Linear Regression
Anchored By:
Renu Rao
Kaveh Saba
Features of Solver
Solver can optimize the value in the target cell by varying the adjustable cells.
Cells are related by a given model equation, which is then used to find predicted values.
NonLinear Regression
Two-variable nonlinear regression 3 components:
Experimental Data A model equation to follow to get predicted
values Initial guesses for variables values.
In the Excel Spreadsheet…
Column 1: Independent variables from experimental data
Column 2: Values measured for the dependent variables
Column 3: Predicted values using the model equation
2 initial guesses for variables
Reaction Rate Example from Text
Given: Rate constant (k) at various temperatures (T)
Need to determine Activation Energy (E) and frequency factor (A)
Fit to Arrhenius equation k(T) = Ae −E / RT
Initial Excel SetupVarying Values:
A (s-1): 2000.0E (J) 25000.0
Constants:R (J/mol*K): 8.314
Prediction Model: k = A*exp(-E/(R*T))
Temperature (K) Experimental Rate constant (s-1) Predicted k value Residuals Squared301 0.403 0.091716623 0.096897341322 0.709 0.175953962 0.284138078339 0.996 0.281046883 0.511157959357 1.464 0.439551663 1.049494395383 2.234 0.778637023 2.118081395406 3.222 1.214772938 4.028960479421 4.013 1.581606675 5.911673502438 5.13 2.086858925 9.260707605
Sum of Residuals Squared: 23.26111075
Thank You for viewing our presentation!
Renu and Kaveh