You have n pairs of (x,y) data; your task is to fit this data to the
equation.
We do this by finding the values of the coefficients b_{1}, b_{2}, …, b_{k} that give the minimum sum of the squares of the residuals between the given and the computed y-values (i.e. the least-squares fit).
Assuming your worksheet has had the x- and y-values entered, the next step is to construct the X matrix which, for n pairs of (x, y) values and k coefficients will of size n by k. The elements of the matrix will be as shown in the table below. If the first term in the equation for y is the intercept then f_{1}(x) will be unity (the number 1) so this will be the value in each element of the first column.
f_{1}(x_{1}) |
f_{2}(x_{1}) |
f_{3}(x_{1}) |
… |
f_{k}(x_{1}) |
f_{1}(x_{2}) |
f_{2}(x_{2}) |
f_{3}(x_{2}) |
… |
f_{k}(x_{2}) |
… |
… |
… |
… |
… |
f_{1}(x_{n}) |
f_{2}(x_{n}) |
f_{3}(x_{n}) |
… |
f_{k}(x_{n}) |
In the figure below we see an example of fitting 6 data points to a quadratic equation (3 coefficients).
This documents is not the place to offer a proof but it can be shown that one can generate a matrix (here called B) of the coefficients using the formula B = (X^{T}X)^{-1}·X^{T}Y.
In the top part of figure above we have the (x,y) pairs, the X matrix as just described and its transpose X^{T}. The column of y-values constitutes the Y matrix. In the lower part we first compute X^{T}X and then the inverse (X^{T}X)^{-1}. We next compute X^{T}Y and finally we get B from B = (X^{T}X)^{-1}·X^{T}Y.
The file NonLinearFit.xlsx contains three examples: fitting a linear equation y = b_{1} + b_{2}·x, fitting a quadrat equation y = b_{1} + b_{2}·x+b_{3}·x², and a log_{10}(P)= b_{1} + b_{2·}(1/T) + b_{3}·log_{10}(T) + b_{4}·T². The latter reveals the true value of this method: Solver is unable to get even a reasonable fit.