1

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

$$y=a+be^{cx}$$

Modified for my purposes as

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

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

I've come up with an initial estimate for $a$

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:

I want to tune the estimate using the Newton method (as described here ) to minimize the ESS. How can I do that?

User1974
  • 425

1 Answers1

1

With the help of a colleague, I was able to tune the estimate using three Newton-Raphson iterations:

The (n+1)th iteration is given by:

$$a_{n+1}=a_n-\frac{f(a_n)}{f'(a_n)}$$

Initial approximation $_0$ = 0.147233112 (coefficient found through Normal Equations)

$$f(a)=\sum_{i=1}^n \left(21-e^{ax_i}-y_i \right)e^{ax_i}x_i$$

$$f'(a)=\sum_{i=1}^n \left(21-2e^{ax_i}-y_i \right)e^{ax_i}x_i^2$$

We stop when further iterations don't lead to a further reduction in the ESS. In this case, only three iterations were needed.

Excel 2016:

enter image description here

enter image description here

Formulas:

C9     =21-EXP($C$33*A9)
D9     =(C9-B9)*EXP($C$33*A9)*A9
E9     =(21-2*EXP($C$33*A9)-B9)*A9*A9*EXP($C$33*A9)
F9     =21-EXP($F$33*A9)     
G9     =(21-EXP($F$33*A9)-B9)*EXP($F$33*A9)*A9
H9     =(21-2*EXP($F$33*A9)-B9)*A9*A9*EXP($F$33*A9)
I9     =21-EXP($I$33*A9)
J9     =(21-EXP($I$33*A9)-B9)*EXP($I$33*A9)*A9
K9     =(21-2*EXP($I$33*A9)-B9)*A9*A9*EXP($I$33*A9)
L9     =21-EXP($L$33*A9)
M9     =IF(L9<1, 1, L9)
N9     =21-EXP($N$33*A9)

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


F33    =C33-(D30/E30)
F34    {=SUM(VALUE(  ($B$9:$B$29 - F9:F29)^2  ))}


I33    =F33-(G30/H30)
I34    {=SUM(VALUE(  ($B$9:$B$29 - I9:I29)^2  ))}


L33    =I33-(J30/K30)
L34    {=SUM(VALUE(  ($B$9:$B$29 - L9:L29)^2  ))}


M33    =L33
M34    {=SUM(VALUE(  ($B$9:$B$29 - M9:M29)^2  ))}


N33    Static value from Excel Solver. Screenshot: https://i.stack.imgur.com/CfI3k.png
N34    {=SUM(VALUE(  (B9:B29 - N9:N29)^2  ))}

Notes:

As mentioned, I can't take credit for the calculus stuff. A colleague helped me with that.

I'd be happy to hear about any mistakes or possible improvements. Layman's terms would be appreciated.

User1974
  • 425