I wrote a program that calculates the best fit in VBA excel for the following model $$ y_k=c_1x_k+c_0+c_{-1}(x_k)^{-1} $$ solving for the best fit parameters $c_1$, $c_0$, and $c_{-1}$. However I wrote several custom subroutines to accomplish this. One subroutine calculates the moments of $x$ and $y$, another builds the augment matrix from the moments and calculates the matrix inverse solution using a custom row reduced echelon form.

Are there functions I can use in VBA excel that will handle these calculations without passing any data to the excel spreadsheet and can handle negative powers in the regression model?


I doubt that such a built-in function exists in MS Excel. Nevertheless, this problem is a linear regression that is simple enough to solve analytically.

Let us start with

$$\Pi = \sum_{i=1}^{n} \left[y_i - \left(a x_i + b + \frac{c}{x_i}\right)\right]^2 \enspace $$

with $n$ the number of data pairs and $(a,b,c)\equiv (c_1, c_0, c_{-1})$. To find the minimum of this approximation we compute the partial derivatives

$$\frac{\partial \Pi}{\partial a} = 0\\ \frac{\partial \Pi}{\partial b} = 0\\ \frac{\partial \Pi}{\partial c} = 0 \enspace ,$$

obtaining the system of equations $$\sum x_i y_i = a\sum x_i^2 + b\sum x_i + c\sum 1\\ \sum y_i = a\sum x_i + b\sum 1 + c\sum \frac{1}{x_i}\\ \sum \frac{y_i}{x_i} = a\sum 1 + b\sum \frac{1}{x_i} + c\sum \frac{1}{x_i^2} \enspace .$$

If we simplify the notation with $$\begin{align} &m_1 = \sum x_i y_i, &m_2=\sum y_i,\quad &m_3 = \sum\frac{y_i}{x_i},\\ &\gamma_1 = \sum x_i^2, &\gamma_2 = \sum x_i,\quad &\gamma_3 = \sum \frac{1}{x_i},\\ &\gamma_4 = \sum \frac{1}{x_i^2}, & & \end{align}$$

the system of equations can be written as $$\begin{pmatrix}{\gamma}_{1} & {\gamma}_{2} & n\\ {\gamma}_{2} & n & {\gamma}_{3}\\ n & {\gamma}_{3} & {\gamma}_{4}\end{pmatrix} \begin{pmatrix} a\\ b\\ c\end{pmatrix} = \begin{pmatrix} m_1\\ m_2\\ m_3\end{pmatrix} \enspace .$$

And the solution is given by $$\begin{align} a &= \frac{1}{\Delta}[m_3 n^2 + \left(-m_1\gamma_4 - m_2\gamma_3\right)n + \gamma_2\left(m_2\gamma_4 - m_3\gamma_3\right) + m_1\gamma_3^2]\\ b &= \frac{1}{\Delta}[m_2 n^2 + \left(-m_1\gamma_3 - \gamma_2 m_3\right)n + \gamma_1\left(m_3\gamma_3 - m_2\gamma_4\right) + m_1\gamma_2\gamma_4]\\ c &= \frac{1}{\Delta}[m_1 n^2 + \left(-\gamma_1 m_3 - m_2\gamma_2\right)n - m_1\gamma_2\gamma_3 + \gamma_1 m_2\gamma_3 + \gamma_2^2 m_3] \end{align}$$

with $\Delta = n^3+\left(-\gamma_1\gamma_4 - 2\gamma_2 \gamma_3\right)n + \gamma_2^2 \gamma_4 +\gamma_1 \gamma_3^2$.

  • $\begingroup$ I am glad our calculations agree. Thanks for the answer. I could use some help with this model. The data I am fitting to is $(y_k)^(-1/2)$, which has proven to be very unstable for small $y_k$ with small variations $\Delta y_k$. I discovered that changing the smallest value by 0.001% makes the model impossible to fit, becoming a huge divergence from fitting the experimental data. The only solution I have is to window the data and cut out the really small values of $y_k$. May you provide some suggestions on how to design around these sensitivities? $\endgroup$ Jun 30 '15 at 21:09
  • $\begingroup$ @linuxfreebird I don't understand what your saying. Maybe you should ask another question. $\endgroup$
    – nicoguaro
    Jul 1 '15 at 15:22
  • $\begingroup$ Sorry for the typo. The data is the repecrical square root of the data, $y^{-1/2}$. Small variations for small values of $y_k$ explode the values of $y^{-1/2}$. This makes the data hard to fit. $\endgroup$ Jul 1 '15 at 19:52
  • $\begingroup$ @linuxfreebird I still suggest you to post a new question with those issues. So you can describe correctly what you want and note people see it. $\endgroup$
    – nicoguaro
    Jul 9 '15 at 14:34
  • $\begingroup$ @nicoguaro does not: $$\sum x_i y_i = a\sum x_i^2 + b\sum x_i + c\sum 1\\ \sum y_i = a\sum x_i + b\sum 1 + c\sum \frac{1}{x_i}\\ \sum \frac{y_i}{x_i} = a\sum 1 + b\sum \frac{1}{x_i} + c\sum \frac{1}{x_i^2} \enspace .$$ has to be $$\sum x_i y_i = 2a\sum x_i^2 + b\sum x_i + c\sum 1\\ \sum y_i = a\sum x_i + b\sum 2 + c\sum \frac{1}{x_i}\\ \sum \frac{y_i}{x_i} = a\sum 1 + b\sum \frac{1}{x_i} + 2c\sum \frac{1}{x_i^2} \enspace .$$ ???? $\endgroup$ May 2 '16 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.