1

I have an exponential regression equation that I use to predict the future condition of roads:

$$y=21-e^{a x}$$

I've come up with an initial estimate for $a$ using the normal equation.

$$a=\frac{\sum_{i=1}^n x_iz_i } { \sum_{i=1}^n x_i^2 }=\frac{\sum_{i=1}^n x_i \log(21-y_i)} { \sum_{i=1}^n x_i^2 }$$

enter image description here

C9     =21-EXP($C$33*A9)
C33    {=SUM(VALUE(A9:A29*LN(21-B9:B29))) / SUM(VALUE(A9:A29^2))}
C34    {=SUM(VALUE(  (B9:B29 - C9:C29)^2  ))}

Question:

What are the options for tuning the exponential regression estimate to optimize the coefficient $a$? In other words, I want to minimize the Error Sum of Squares (ESS).

  • I’m wondering what the options are, not because the options I’ve found so far have problems, I just want to explore what different methods are possible as as a learning exercise.

  • I'm looking for options that are reproducible/explainable. I want to be able to show my colleagues how the math was done so that they can thoroughly understand where the results came from. In other words, I'm looking for options that aren't a black box.

  • My preference is to do the calculations in Excel using basic formulas. Reason: My department is an Excel shop (we also have access to Python through GIS tools). But we don't have access to statistical software. So, installing software, even if it's free, is difficult due to IT security restrictions.

    • Edit: To my surprise, I was able to install R without admin privileges on Windows 10. Cool, although I don't know how to use it yet. Download.

  • A side-benefit to using simple Excel formulas is that we can migrate the logic to other programming languages if we need to (such as Oracle SQL).

With that said, I'm open to other ideas if they have significant benefits.

Here are the options I've come across so far:

  1. Excel: Newton-Raphson iterations (Calculus)
    • Uses basic Excel formulas; relatively easy to explain. Can be migrated to other programming languages.
  2. Excel: data table (hit and trial)
    • Works, but is somewhat labor intensive. And is somewhat of a black box.
  3. Excel Solver using nonlinear regression

As someone who is inexperienced with math/stats, are there other options that I might have overlooked?

User1974
  • 425
  • 1
    What do you mean by "tuning"? The value of $a$ you get from the normal equations is the value that minimises the squared error of your model. This is a standard decision criterion in practice. Why would you change $a$ after selecting that value? – Jose Avilez Jan 10 '22 at 16:34
  • @JoseAvilez Good question. I edited the post. I want to minimize the Error Sum of Squares (ESS). For example, I can minimize the ESS via Newton iterations or Excel Solver. And I'm wondering if those were my only options, or if there are others. – User1974 Jan 10 '22 at 16:41
  • The normal equations give you the value of $a$ that minimises the ESS for your model. There is no need to tune any further. – Jose Avilez Jan 10 '22 at 16:42
  • @JoseAvilez Forgive my ignorance, but it's my understanding that the value of $a$ from the normal equation (8.27990876) can be reduced to 6.665716708, using Newton iterations, Excel Solver, Excel data table (hit and trial) etc. But it's entirely possible that I've misunderstood something. – User1974 Jan 10 '22 at 16:48
  • 1
    Apologies, I now realise what you mean. The value of $a$ you get from the normal equations is the value that minimises the error given by $| ax - \log (21 - y) |_2^2$, but not necessarily the error given by $| y - (21 - e^{ax}) |_2^2$. I think your approach of taking an initial solution via normal equations and then optimising the secondary loss function via Newton-Raphson is fine. – Jose Avilez Jan 10 '22 at 17:31
  • @JoseAvilez Do you think the title or wording of the post should be improved? For example, should the title be something like "Normal equation solution: Options for optimizing secondary loss function?" Or something else? Also, feel free to edit the post, if that's easiest. – User1974 Jan 10 '22 at 17:36
  • The title is fine – Jose Avilez Jan 10 '22 at 17:38

1 Answers1

1

If you are trying to minimize the ESS, the given exponential equation is the best one. That is, Excel has given you the absolute best exponential equation possible. That being said, there are 2 options for producing "better" results, or more what I assume you are looking for.

Firstly, you could perhaps try to smooth out or fix any errors in your data. There seems to be a number of spikes and inconsistencies. The best way to fix this issue is to gather more data and use an average. However, that may not be feasible, which is why there's another way.

Excel has a wonderful built in trendline menu. Open the "Format Trendline" menu and under "Trendline Options" select "Polynomial". Increase the order to at least 3 and possibly 4 or even higher. To see the formula select the "Display Equation on chart" (although I believe you already have done this). Additionally, you could select the "Display R-squared value on chart". You can play around with settings like the order and observe this R-squared value. The closer it is to 1, the better. You should then have a clear equation that's easy to work with.

Caedmon
  • 560
  • 2
  • 10