Experiment
Multiple linear and non linear regression by using MS excel/Libre office calc
Aim: Regressing rate constants in rate equation from experimental data by linear and non linear
regression by using MS excel/Libre office calc
Theory:
Regression analysis mathematically describes the relationship between a set of independent variables and
a dependent variable.
In statistics, linear regression is a linear approach to modeling the relationship between a dependent
variable and one or more independent variables.
The case of one explanatory variable is called simple linear regression.
For more than one explanatory variable, the process is called multiple linear regression.
In linear regression, the relationships are modeled using linear predictor functions whose unknown
model parameters are estimated from the data. Such models are called linear models.
Linear regression has many practical uses. Most applications fall into one of the following two broad
categories:predicting and variation in response.
Linear regression models are often fitted using the least squares approach.
There are several types of linear regression analyses available to researchers.
Simple linear regression:1 dependent variable , 1 independent variable
Multiple linear regression
1 dependent variable (interval or ratio) , 2. independent variables (interval or ratio or dichotomous)
Nonlinear regression
In statistics, nonlinear regression is a form of regression analysis in which observational data are modeled by
a function which is a nonlinear combination of the model parameters and depends on one or more
independent variables. The data are fitted by a method of successive approximations.
y = ax2 + b
Engineers often need to express experimental data in terms of an equation. They must decide on the equation
and then determine the parameters that provide the best fit to the data. The problem is simplest if the
equation is linear.
The mission will a little bit harder if the best fit equation is polynomial one. Although this later case can be
considered easy, as long as the equation relates 2 variables only. In this part we will discuss how to fit any
set of functions of more than 2 parameters
either appearing linearly (multiple linear regression), or nonlinearly (multiple non-linear
regression).
Multiple regression using Excel
Early in the introduction of the course it was shown how to fit a polynomial to data. The same thing can be
done when the functions are not simple powers, but are more complicated functions.
However, to keep the problem linear, the unknown coefficients must be coefficients of those functions; that
is, the functions are completely specified. Multiple regression simply determines how much of each one is
needed. Thus, the form of the equation is
The goal is to find the best M values of ai, given the M functions fi (x) and data yi = y
(xi), i =1,. . ., N
In Excel, you put the x values in a column and create additional columns, with each column being a
function, evaluated for the x value in that row. The example used here is to find the constants in a
reaction rate formula. The expected expression is
and the goal is to find the values of k, n, and m that give the best fit of the rate for various partial
pressures of substances A and B. This form is not linear, which is a requirement of multiple
regression, but a transformation can make it linear. Take the logarithm of both sides of the equation
This equation has the following form:
where the dependence upon two or more variables is clear.
Procedure:
The data is entered into the libre office calc,and the various terms are transformed as shown in next table.
Columns A and B are the partial pressures of the two chemicals for which the rate is measured, as
indicated in column C.
Pa Pb
0.1044 0.1036
0.1049 0.2871
0.103 0.5051
0.2582 0.1507
0.2608 0.31
0.2407 0.4669
0.3501 0.0922
0.3437 0.1944
0.3494 0.5389
0.4778 0.1017
0.488 0.258
0.5014 0.5037
Step 1 :Obtain columns H, I, and J by taking the logarithm of columns E, F, and G, respectively.
Step 2: Proceed with parameter estimation by choosing -Data-statistics-regression
Step 3:Enter J10:F21 for the y values and H10:I21 for the x values. This tells the computer that you want the
best line representing ln(rate) depending linearly upon ln pa and ln pb.
Step 3:The result will appear like this:
The yellow colored cell denotes values of c,b,a
c= 0.1895,b=0.98,a=1.96
k = ea = e1.96= 7.09
The curve fit is then
rate = 7.09 PA0.98 PB0.1895
Non linear regression:
Nonlinear regression is a curve fit in which the unknown parameters enter into the problem in a
nonlinear way.For nonlinear curve fits it is necessary to use functions such as Solver in Excel. This is
demonstrated using the same example given above for multiple regression
Procedure:
Step 1: Place the data on a new sheet as shown in next figure
Step 2 :Enter arbitrary values for the parameters k, n, and m.
Step 3: In column K, calculate the value of rate using the parameters in H73:H75, the data in
columns E and F, and the formula, Eq.
Step 4 :Make column L the difference between columns K and G, and then square the result and put
it in column M.
Step 5 :Sum Column M, divide by the number entries [COUNT (M60:M71)] to obtain the (sum of
squares)/N.
Step 6 :The goal is to minimize D72 by choosing values H73:H75. To do that, choose
Tools/Solver.
In target cell,enter cell N72,In by changing cells-enter H73:H75.Select optimize results to minimum.
Go to options and select DEPS evolutionary algorithm.Click solve.
The result is :
Result:
Rate equation by linear and non linear regression is :
