What are the formulas for exponential, logarithmic, and polynomial trendlines?

Question

In creating linear trendline, I used the following formulas:

$$y=mx+b$$ $$m = \frac{n\sum(xy)-\sum x \sum y}{n\sum x^2 - (\sum x)^2}$$ $$b = \frac{\sum y- m \sum x}{n}$$

and this for the R-squared:

$$ R^2 = 1 - \frac{\sum[y-(mx+b)]^2}{\sum(y-\bar{y})^2}$$

My goal is to create the trendlines via programming, so I need the formulas to create them. However, I cannot find any resources for the exponential, logarithmic, and polynomial trendlines. Is there a good resource that you could refer to me for me to study?

(BTW, I'm not really into statistics so it'll take time for me to understand.)

Answer 1

I've come to the conclusion that you're probably trying to reproduce what Excel does.

This is not necessarily sensible, but at least it's pretty straightforward.

Here's what Excel does:

• linear trendline: ordinary simple regression, fitted by least squares

• logarithmic trendline: $y \sim a + b \ln(x)$ -- fitted by taking $x'=\ln(x)$ & using ordinary linear least squares on the new $x$-variable (i.e. fitting $E(y)=a+bx'$ using least squares as above). (Here the symbol $'$ simply denotes the new, transformed variable; it's not intended to indicate a derivative or anything)

This would be reasonably appropriate if the spread about the curve was roughly constant:

Transforming the $x$-variable doesn't alter the spread at each $x$.

• exponential trendline: $y \sim ae^{bx}$ -- fitted by taking $y'=\ln(y)$ and using least squares $E(y')=a'+bx$, then exponentiating the intercept to obtain $a=\exp(a')$ , and exponentiating the log-scale fit to obtain fitted values.

By transforming $y$, we change the spread about the curved relationship. On the log-scale, where we're fitting a straight line by least squares, the spread is assumed constant on that scale, which implies it's proportional to the mean of $y$ on the scale of the original data:

Note that the fitted values on the original scale will be biased for the mean (if the distribution is symmetric on the log-scale the curve will instead estimate the median).

• power: $y \sim ax^b$ -- fitted by taking logs of both x and y and using least squares to fit a straight line $E(y')=a'+bx'$, then exponentiating the fitted intercept to obtain $a$, and exponentiating the log-scale fit to obtain fitted values. (You didn't ask for this one, you can have it for free.)

As with the exponential trend, by transforming $y$, we change the spread about the curved relationship. On the log-scale, where we're fitting a straight line by least squares, so the model is best suited to when the spread about the curve is proportional to the mean of $y$ on the scale of the original data. The picture for that case looks broadly similar to the above situation where the spread is wider when the mean is larger. (This also has the same mean-bias issue as the exponential fit.)

• polynomial: least squares polynomial fit using ordinary multiple regression on powers of $x$. (For better numerical behavior, use orthogonal polynomials and convert back.)

Somewhat similar to the log-trend case, this is most suitable when the spread is constant about the curved relationship, but unlike the log-trend case this can deal with a wider range of curved relationships.

In the case of polynomial regression it's important to avoid too high an order of polynomial; polynomial fits can be quite unstable, in some cases they can be heavily affected by small changes in a few points.

So in fact, aside from the polynomial fit (which is itself simply a multiple regression problem), if you can fit a simple least squares line, you can emulate Excel by doing a simple transformation of one or both of $x$ and $y$ and using least squares (and possibly doing some simple transformation back the other way in some cases). As such, you already have the formulas for those cases.

(If you want something more sensible than what Excel does, you may need to ask a different question, and then issues like those I raised in comments may become important.)

Answer 2

You can use the forumla for calculating the least squares coefficients of a regression with multiple independent variables. See here for a detailed explanation.

Basically they all reduce down to a single matrix equation given by the formula:

$$\hat\beta = (X'X)^{-1}X'y$$

where

$\beta=\begin{bmatrix}\beta_0 \\ \beta_1 \\ . \\. \\. \\ \beta_n \end{bmatrix}$, $ X=\begin{bmatrix}1 & x_{11} & x_{21} & . & . & . & x_{n1}\\1 & x_{12} & x_{22} & . & . & . & x_{n2}\\. & . & . & . & & & .\\. & . & . & & . & & .\\. & . & . & & & . & .\\1 & x_{1m} & x_{2m} & . & . & . & x_{nm}\\\end{bmatrix}$, $y=\begin{bmatrix}y_0 \\ y_1 \\ . \\. \\. \\ y_m \end{bmatrix}$

and $m$ is the number of observations you have, $n$ is the degree of the polynomial curve you wish to fit. Note that with a log or exponential curve $n$ will just be $1$.

And the idea is that your $X$ matrix can have transformations of your original $x$ independent variable rather than totally new variables. So for example for a polynomial curve your $X$ matrix has as its columns your original $X$, and then that same data repeated but now raised to different powers depending on the degree of the curve you're looking to fit (this you have to decide before hand). If you want an offset like your $b$ then you'll need to add a column of just the number $1$ (i.e. to get an offset).

So for a polynomial curve your $X$ matrix becomes:

$$X=\begin{bmatrix}1 & x_{1} & x_{1}^2 & . & . & . & x_{1}^n\\1 & x_{2} & x_{2}^2 & . & . & . & x_{2}^n\\. & . & . & . & & & .\\. & . & . & & . & & .\\. & . & . & & & . & .\\1 & x_{m} & x_{m}^2 & . & . & . & x_{m}^n\\\end{bmatrix}$$

For an exponential curve / log curve

$$X=\begin{bmatrix}1 & e^{x_1}\\1 & e^{x_2}\\. & .\\.& .\\. & .\\1 & e^{x_m}\\\end{bmatrix}, X=\begin{bmatrix}1 & \ln{x_1}\\1 & \ln{x_2}\\.& . \\.& .\\.& . \\1 & \ln{x_m}\\\end{bmatrix}$$

Note that these equations are still linear (in that they take the form $y=\beta X$) and thus of the exact same form as the linear curve formula you've already solved. i.e. for the log curve we get $y = \beta_1 \ln{x} + \beta_0$

Sometimes it is impractical to use the above closed form solution. This can happen if your $X$ matrix is very large (around say $100 000$ variables, even more rows). In these cases you can consider the alternative iterative approach to solving the least squares problem. i.e using this same set up of your $X$ matrix, instead of using the formula to solve for $\beta$, you can use an iterative optimization method such as gradient descent (or others such as fmincon in Matlab or the solver in Excel). Here you would be trying to minimize:

$$\sum_1^m\left((\beta_0+\beta_1\overrightarrow{x}_1+...+\beta_n\overrightarrow{x}_n)-\overrightarrow{y}\right)^2$$

i.e.

$$\sum_1^m\left(\beta X - y\right)^2$$

where

$\overrightarrow{x}_k=\begin{bmatrix}x_{k1} \\x_{k2}\\ . \\. \\. \\ x_{km} \end{bmatrix}$

See this post for a more detailed comparison of these two alternatives.

Answer 3

Well, I think that you mean only linear models? Otherwise, as Glen_b has said, the list of models would be uncountable. Just to give you a hint, in statistics we call models linear if they are linear in their parameters. For example, the parameters (in your trend model from the picture) are $m$ and $b$, thus it is a linear model. Now, let us go to quadratic in terms of $x$, which is still $linear$ in statistical terms --- that would be: $y = mx + m_1x^2 + b$. Here I have introduced the extra parameter $m_1$ that would be governing the quadratic..Thus, in the $R^2$ formula, you would get: $R^2 =1 - \frac{\sum[y - (mx + m_1x^2 + b)]^2}{\sum(y - \bar{y})^2}$. Analogously, you could get similar expressions for any model(e.g. log or cubic) in terms of $x$, but be careful what and how many your parameters are. Hope this helps.

Answer 4

Use Linearizing transformation. $$y=m\ln{x}+b$$ All you do is replace $x$ with $\ln{x}$ in all your expressions for $m$ and $b$, e.g. $$b = \frac{\sum y- m \sum \ln{x}}{n}$$ The same thing with exponential: replace $x$ with $\exp{(x)}$.

Polynomial become a bit more involved, but the same idea. You have to apply ordinary least squares in the way that is shown in this example, i.e. $$y=X\beta$$where $X$ is the column matrix of your dependent variables. In this case the first column is all ones, the second column is $x_i$, the third is $x_i^2$, the fourth is $x_i^3$ and so on. Then your estimated parameters are $$(X'X)^{-1}X'y$$. You'll have to use matrix calculations library, don't even try to implement matrix algebra yourself.

To whuber's point,you have to plug your transformed variable in $R^2$ too: $$ R^2 = 1 - \frac{\sum[y-(m\ln{x}+b)]^2}{\sum(y-\bar{y})^2}$$