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 of the other types of trendlines that Excel produces 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$ Because an exponential is a terrible description of the shape of your data. If might help you to understand if you plotted out exponential functions and played with the parameters to see what they look like. $\endgroup$ – mkt Nov 24 '17 at 1:25
  • 2
    $\begingroup$ Yea, @Wilson , probably the wrong forum for this. Understanding how linear regression works would be the question you should be asking yourself, and there are plenty of resources on this site for that. If in the process of learning the mechanics you determine you don't understand something, check to see if the question has been asked before and if it hasn't then ask it here. Generally questions won't be well received if they are of the form "Why didn't this particular function/algorithm/model fit my data well". $\endgroup$ – David Kozak Nov 24 '17 at 3:17
  • 1
    $\begingroup$ @DavidKozak Fair enough. Good feedback. $\endgroup$ – Wilson Nov 24 '17 at 3:19
  • 2
    $\begingroup$ An interesting thing about the polynomial trendline is that if $y$ must be non-negative, it will quickly cease to be valid for values of $x$ much larger than 20. This isn't necessarily bad, but it highlights an important distinction between using a fit to describe the data you have (which the polynomial seems to do well), and to understand or predict the data, which the polynomial (and the exponential) might do spectacularly poorly. This is why we like to know more about your data and your objectives before venturing answers to your questions. $\endgroup$ – whuber Nov 24 '17 at 13:58
  • 1
    $\begingroup$ @whuber Your points are fair. In a related question, I provided more details, but got absolutely no interest in the question, even with a bounty. So, in an attempt to get a better response, I boiled my issue down to a simple example. $\endgroup$ – Wilson Nov 24 '17 at 14:18
6
$\begingroup$

A function of the form $m_Y = \exp(a+bx)$ (where $m$ represents some conditional population coefficient of interest, like a mean, geometric mean, or a median, perhaps, depending on how your error term is set up) is convex, while your data are not:

Plot of example exponential curves

The plot shows two such curves, one with a negative coefficient on $x$, and one with a positive coefficient on $x$. By changing parameters, you can move the curves left or right and you can make them steeper or shallower (in essence, changing the axis scale), but this basic shape is what you get with exponential curves.

There are other functions containing exponential terms that might fit something like your data more or less okay, but you should not expect a convex function to fit data that are clearly nothing like convex.

$\endgroup$
2
$\begingroup$

My concern with your model is that you may be ignoring important mathematical properties and get bad predictions. For example, I am assuming that roads cannot spontaneously improve themselves. Roads do not get better on their own. This matters.

It may be the case that no pre-built Excel model will be a good predictive fit. It seems like roads remain quite good for a long time, but once they begin deteriorating they begin to come apart faster and faster. Although this loosely implies a quadratic-like model, the maximum value should always be for a new road and it is not under your specification. The best road happens in year two.

Your model also does not consider changes in weather or the comparability of building materials from twenty years ago to today.

Finally, your dependent variable is probably ranked data. Is a road rated as a 10 actually 10 times better than one whose rating is a 1 and is one rated a 10 exactly 2 times better than one rated a 5?

I am a little concerned that your model may be fragile for the purposes of making a prediction. Excel really isn't designed with this in mind and doesn't have the functions for this. It doesn't really go past simple models. While you may be experiencing exponential decay, your rating system won't work with that.

$\endgroup$

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.