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.)

  • $\begingroup$ There are an infinite number of nonlinear functions so you've asked for an (uncountable) infinity of formulas. I think you need to narrow things down in some fashion. Can you ask a different form of question, perhaps? $\endgroup$
    – Glen_b
    Jan 23, 2015 at 1:47
  • $\begingroup$ Most non-linear functions will not have closed-form solutions for your coefficients (as you do for m and b here). Often, numerical analysis is employed. Does your programming language have a library for performing non-linear regression? (What language are you using?) $\endgroup$
    – Jeff
    Jan 23, 2015 at 2:41
  • 1
    $\begingroup$ @LesterNubla Here is an open-source Javascript library that will find linear, exponential, polynomial, logarithmic, and power trend lines. $\endgroup$
    – Jeff
    Jan 23, 2015 at 4:40
  • 2
    $\begingroup$ The "exponential, logarithmic, and polynomial" part describes the behavior of the mean, but the data don't just consist of a mean; to obtain a suitable fitting method you need to have some kind of assumption about how the data behave around the mean. $\quad$ Consider these data, which all have exponential trends: $\quad$ Three exponential curves The first has constant spread about the epxonential curve, and would be suitable for fitting using nonlinear regression with constant variance. $\quad$ ... (ctd) $\endgroup$
    – Glen_b
    Jan 23, 2015 at 4:54
  • 2
    $\begingroup$ (ctd)... The second has spread proprotional to the mean and a suitable fit would involve taking logs and fitting a straight line (before exponentiating back, though if a mean-fit on the original scale is required, it also needs a bias correction after backtransforming). $\quad$ The third is count data, and has spread proportional to the square root of the mean; it would be best fitted by using a Poisson (or perhaps quasi-Poisson) GLM. $\quad$ In spite of needing different fitting methods, all three have exactly the same (population-)mean curve about which the points lie. $\endgroup$
    – Glen_b
    Jan 23, 2015 at 4:54

4 Answers 4


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:

enter image description here

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:

enter image description here

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.)

  • 1
    $\begingroup$ +1 I was waiting for someone to point simple excel functions $\endgroup$
    – forecaster
    Jan 27, 2015 at 12:10

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


$\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 X - y\right)^2$$


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


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.


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}$$

  • $\begingroup$ There is a hidden change in error structure among these three models. The meaning of $R^2$ will differ among them as a consequence. You might therefore consider pointing out and explaining what's really going on here. $\endgroup$
    – whuber
    Jan 26, 2015 at 17:40
  • $\begingroup$ @whuber, I'm not sure what you mean by error structure here. When transformation is applied to dependent variable, it's a complete change in error, such as additive vs. multiplicative in case of log. When you simply transform X, yes, the error is different, but it's not structurally different, whatever it means $\endgroup$
    – Aksakal
    Jan 26, 2015 at 17:54
  • 1
    $\begingroup$ Yes, it is structurally different. By asking for $R^2$ the OP is implicitly assuming models of the form $y=e^b x^m+\epsilon$, for instance, where the error $\epsilon$ is additive. Your "linearizing transformation" ends up with a model that implicitly is of the form $\log(y)=b + m\log(x) + \delta$. But that is far from the same: your error is the same as multiplicative error $e^\delta$ in the original model, not additive error. $\endgroup$
    – whuber
    Jan 26, 2015 at 17:57
  • $\begingroup$ @whuber, no OP is not applying log to the left hand side, so his model is not $y=e^{b+xm}$, it's still $y=b+m\ln x$ $\endgroup$
    – Aksakal
    Jan 26, 2015 at 18:01
  • 3
    $\begingroup$ If you do not transform $y$, then your $R^2$ comment is mysterious, because then the interpretation of $R^2$ would not change at all. You are also using "noise" in a strange sense: normally it would refer to the error term for $y$, not for an explanatory variable. If you are thinking of the independent variables as random, then it seems like we're right back where we started: your "linearizing transformation" will change their distributions and you ought to make some remarks on the implications of that for the model. (But I doubt the OP is asking about mixed models.) $\endgroup$
    – whuber
    Jan 26, 2015 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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