Options for optimizing exponential regression

Question

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

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
   • Works, but is a black box. I don't have access to information about how the calculations were done.
   • Related: Mimic Excel Solver nonlinear regression? (to reduce ESS of exponential regression)

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

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