# Power series regression linear fit in VBA excel

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.

$$\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$$.

• 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? Commented Jun 30, 2015 at 21:09
• @linuxfreebird I don't understand what your saying. Maybe you should ask another question. Commented Jul 1, 2015 at 15:22
• 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. Commented Jul 1, 2015 at 19:52
• @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. Commented Jul 9, 2015 at 14:34
• @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 .$$ ???? Commented May 2, 2016 at 9:16