0
$\begingroup$

I'm attempting to generate an exponential regression equation using Excel 2010. I plan to use the equation to predict the future condition of roads.

I've mocked up a simple chart in Excel using the data below.

AGE   CONDITION
0     10
1     10
2     10
3     10
4     10
5     10
6     9
7     9
8     9
9     9
10    8
11    8
12    7
13    7
14    6
15    6
16    5
17    4
18    3
19    2
20    1

enter image description here


The orange line is based on the data. The black line is a regression trendline that has been auto-generated by Excel.

Problem:

It's clear to me just by looking at the graph that the exponential trendline does not match the data. In comparison, all other types of Excel trendlines match the data fairly well.

For example, this polynomial trendline seems to fit the data like a glove:

enter image description here

Why does the exponential trendline not match the data?

$\endgroup$
3
  • $\begingroup$ Why would a data set match the exponential law??? You ought not to question facts! $\endgroup$
    – zoli
    Nov 24, 2017 at 0:12
  • 1
    $\begingroup$ Exponential fits in Excel are of the form $y=ae^{bx}$. What you have is a vertical translation of this expression. You need an additive constant to fit, or translate the graph so that its horizontal asymptote is the x axis. $\endgroup$
    – Paul
    Nov 24, 2017 at 0:29
  • 1
    $\begingroup$ @Paul Thanks. This comment really helped me (both back in 2017 and currently). $\endgroup$
    – User1974
    Jan 3, 2022 at 2:17

1 Answer 1

3
$\begingroup$

Just as Paul commented, using $$y=a+b\,e^{cx}$$ a nonlinear regression leads to $$\begin{array}{clclclclc} \text{} & \text{Estimate} & \text{Standard Error} & \text{Confidence Interval} \\ a & 11.0549 & 0.263782 & \{10.4984,11.6114\} \\ b & -0.786277 & 0.155049 & \{-1.1134,-0.459151\} \\ c & 0.128227 & 0.009232 & \{0.108749,0.147705\} \\ \end{array}$$ which is quite good $(R^2=0.999027)$ and produce the following values $$\{10.27,10.16,10.04,9.90,9.74,9.56,9.36,9.13,8.86,8.56,8.22,7.83,7.39,6.89,6.32,5.67,4.94,4.10,3.15,2.07,0.84\}$$ corresponding to a sum of squares equal to $1.24$.

Using your quadratic model, the parameters are good but the sum of squares is equal to $1.41$ and $R^2=0.998893$ (slightly worse).

$\endgroup$
3
  • $\begingroup$ @Wilson. This case was simple. Looking at the data, first, fix $a=11$ or $a=12$; this leaves you with $z=y-a=b e^{cx}$. Make a logarithmic transform of both sides : you have a linear regression which gives you estimates of $b,c$. You have everything ready for starting the trivial nonlinear regression. $\endgroup$ Dec 13, 2017 at 4:13
  • $\begingroup$ @Wilson. Now, if you want a general algorithm for $y=a+b\,e^{cx}$, just ask for it. $\endgroup$ Dec 13, 2017 at 4:15
  • $\begingroup$ @Wilson. May I suggest you ask that in a separate question at MSE or at crossvalidated SE ? $\endgroup$ Dec 13, 2017 at 4:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .