Trendline Extract

[Phil Hageman]

I am calculating/plotting a log trendline extract on the equation
y = 533371Ln(x) - 3E+06. Ln(x) is the period (extract) number, starting at
1 and ending at 288. There are 288 plotted points. The value for 533… and
3E… are derived from the trendline equation each month, when a new value (and
account balance) is added to the series. The plotted extracted values (y)
give me an exact duplicate of the Excel log trendline curve, and, a value
(forcast) for period 288, where the trendline crosses the right side of the
chart. The series looks like this, where cell L2 holds the 533 value, M2
holds the 3E value, and G* is the extract number. Works fine.

=(L2)*LN(G1)-M2
=(L2)*LN(G2)-M2
=(L2)*LN(G3)-M2
etc.
=(L2)*LN(G288)-M2

Now I want to do a second series, using a 2nd order poly trendline with the
equation y = 1.8485x2+641.06x (given by Excel). The extract dimension
remains 1 through 288, and the series is:

=1.8485*(G1)+641.06*(G1)
=1.8485*(G2)+641.06*(G2)
=1.8485*(G3)+641.06*(G3)
etc.
=1.8485*(G288)+641.06*(G288)

The final value (G288) is coming up 183,228, when it should be in the region
of 340,000, the approximate value where the Excel-plotted trendline crosses
the right side of the chart.

Where is the logic flaw?

 

[Tushar Mehta]

On the chart, double click the trendline equation, then select the
Number tab. Change the format to something that shows lots of decimal
places (scientific with 14 decimal places, for example).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Phil Hageman]

Hi Tushar,

Referencing the first example, the log trendline: the "extract" is a way to
make a duplicate of the Excel-created trendline, using the Excel-generated
equation. You now have two trendlines - one on top of the other; however, on
the extract series, all plotted values can be determined - not possible with
normal function of Excel.

That's what I want to do in the second example, the 2nd order polynomial
trendline, is create a second trendline, where I can access the values of
each point on the plotted curve. Do you know how to do this?

As to changing number format, this has no impact on the issue that I can
rationalize. Can you help me further?

Thanks, Phil

 

[Tushar Mehta]

What are the new values? How do they compare to the old values?

Also, the formulas you shared are all missing the square term and the
constant term:

=1.8485*(G1)+641.06*(G1)
=1.8485*(G2)+641.06*(G2)
=1.8485*(G3)+641.06*(G3)
etc.
=1.8485*(G288)+641.06*(G288)

You should have a2*x^2 + a1*x + a0
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Phil Hageman]

Thusar,

Correcting the squared value resolved most of the problem - values are well
in the ball park. As to the third term, Excel is not providing a third term
- is this a normal circumstance?

Phil

 

[Tushar Mehta]

XL will suppress the constant term if it is truly zero or if you've
forced it to be zero (Double-click the trendline, then select the
Options tab).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions