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=20
Number tab. Change the format to something that shows lots of decimal=20
places (scientific with 14 decimal places, for example).

--=20
Regards,

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

In article <[email protected]>,=20
[email protected] says...
> I am calculating/plotting a log trendline extract on the equation
> y =3D 533371Ln(x) - 3E+06. Ln(x) is the period (extract) number, startin=
g at=20
> 1 and ending at 288. There are 288 plotted points. The value for 533=E2=
=A4=3D3F and=20
> 3E=E2=A4=3D3F are derived from the trendline equation each month, when a =
new value (and=20
> account balance) is added to the series. The plotted extracted values (y=
)=20
> give me an exact duplicate of the Excel log trendline curve, and, a value=
=20
> (forcast) for period 288, where the trendline crosses the right side of t=
he=20
> chart. The series looks like this, where cell L2 holds the 533 value, M2=
=20
> holds the 3E value, and G* is the extract number. Works fine.
>=20
> =3D(L2)*LN(G1)-M2
> =3D(L2)*LN(G2)-M2 =20
> =3D(L2)*LN(G3)-M2
> etc.
> =3D(L2)*LN(G288)-M2
>=20
> Now I want to do a second series, using a 2nd order poly trendline with t=
he=20
> equation y =3D 1.8485x2+641.06x (given by Excel). The extract dimension=
=20
> remains 1 through 288, and the series is:
>=20
> =3D1.8485*(G1)+641.06*(G1)
> =3D1.8485*(G2)+641.06*(G2)
> =3D1.8485*(G3)+641.06*(G3)
> etc.
> =3D1.8485*(G288)+641.06*(G288)
>=20
> The final value (G288) is coming up 183,228, when it should be in the reg=
ion=20
> of 340,000, the approximate value where the Excel-plotted trendline cross=
es=20
> the right side of the chart.
>=20
> Where is the logic flaw?
>=20
>=20

[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

>Tshar Mehta" wrote:

> 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
>
> In article <[email protected]>,
> [email protected] says...
> > 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â€=3F and
> > 3Eâ€=3F 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]

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=20
constant term:

=3D1.8485*(G1)+641.06*(G1)
=3D1.8485*(G2)+641.06*(G2)
=3D1.8485*(G3)+641.06*(G3)
etc.
=3D1.8485*(G288)+641.06*(G288)

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

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

In article <[email protected]>,=20
[email protected] says...
> Hi Tushar,
>=20
> Referencing the first example, the log trendline: the "extract" is a way =
to=20
> make a duplicate of the Excel-created trendline, using the Excel-generate=
d=20
> equation. You now have two trendlines - one on top of the other; however=
, on=20
> the extract series, all plotted values can be determined - not possible w=
ith=20
> normal function of Excel.
>=20
> That's what I want to do in the second example, the 2nd order polynomial=
=20
> trendline, is create a second trendline, where I can access the values of=
=20
> each point on the plotted curve. Do you know how to do this?
>=20
> As to changing number format, this has no impact on the issue that I can=
=20
> rationalize. Can you help me further?
>=20
> Thanks, Phil
>=20
> >Tshar Mehta" wrote:
>=20
> > On the chart, double click the trendline equation, then select the=20
> > Number tab. Change the format to something that shows lots of decimal=
=20
> > places (scientific with 14 decimal places, for example).
> >=20
> > --=20
> > Regards,
> >=20
> > Tushar Mehta
> > www.tushar-mehta.com
> > Excel, PowerPoint, and VBA add-ins, tutorials
> > Custom MS Office productivity solutions
> >=20
> > In article <[email protected]>,=20
> > [email protected] says...
> > > I am calculating/plotting a log trendline extract on the equation
> > > y =3D 533371Ln(x) - 3E+06. Ln(x) is the period (extract) number, sta=
rting at=20
> > > 1 and ending at 288. There are 288 plotted points. The value for 533=
=C3=A2=E2=3D3F=AC=3D3F and=20
> > > 3E=C3=A2=E2=3D3F=AC=3D3F are derived from the trendline equation each=
month, when a new value (and=20
> > > account balance) is added to the series. The plotted extracted value=
s (y)=20
> > > give me an exact duplicate of the Excel log trendline curve, and, a v=
alue=20
> > > (forcast) for period 288, where the trendline crosses the right side =
of the=20
> > > chart. The series looks like this, where cell L2 holds the 533 value=
, M2=20
> > > holds the 3E value, and G* is the extract number. Works fine.
> > >=20
> > > =3D(L2)*LN(G1)-M2
> > > =3D(L2)*LN(G2)-M2 =20
> > > =3D(L2)*LN(G3)-M2
> > > etc.
> > > =3D(L2)*LN(G288)-M2
> > >=20
> > > Now I want to do a second series, using a 2nd order poly trendline wi=
th the=20
> > > equation y =3D 1.8485x2+641.06x (given by Excel). The extract dimens=
ion=20
> > > remains 1 through 288, and the series is:
> > >=20
> > > =3D1.8485*(G1)+641.06*(G1)
> > > =3D1.8485*(G2)+641.06*(G2)
> > > =3D1.8485*(G3)+641.06*(G3)
> > > etc.
> > > =3D1.8485*(G288)+641.06*(G288)
> > >=20
> > > The final value (G288) is coming up 183,228, when it should be in the=
region=20
> > > of 340,000, the approximate value where the Excel-plotted trendline c=
rosses=20
> > > the right side of the chart.
> > >=20
> > > Where is the logic flaw?
> > >=20
> > >=20
> >=20
>=20

[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" wrote:

> 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
>
> In article <[email protected]>,
> [email protected] says...
> > 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
> >
> > >Tshar Mehta" wrote:
> >
> > > 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
> > >
> > > In article <[email protected]>,
> > > [email protected] says...
> > > > 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ââ=3F¬=3F and
> > > > 3Eââ=3F¬=3F 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]

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

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

In article <[email protected]>,=20
[email protected] says...
> Thusar,
>=20
> Correcting the squared value resolved most of the problem - values are we=
ll=20
> in the ball park. As to the third term, Excel is not providing a third t=
erm=20
> - is this a normal circumstance?
>=20
> Phil
>=20
> "Tushar Mehta" wrote:
>=20
> > What are the new values? How do they compare to the old values?
> >=20
> > Also, the formulas you shared are all missing the square term and the=
=20
> > constant term:
> >=20
> > =3D1.8485*(G1)+641.06*(G1)
> > =3D1.8485*(G2)+641.06*(G2)
> > =3D1.8485*(G3)+641.06*(G3)
> > etc.
> > =3D1.8485*(G288)+641.06*(G288)
> >=20
> > You should have a2*x^2 + a1*x + a0
> > --=20
> > Regards,
> >=20
> > Tushar Mehta
> > www.tushar-mehta.com
> > Excel, PowerPoint, and VBA add-ins, tutorials
> > Custom MS Office productivity solutions
> >=20
> > In article <[email protected]>,=20
> > [email protected] says...
> > > Hi Tushar,
> > >=20
> > > Referencing the first example, the log trendline: the "extract" is a =
way to=20
> > > make a duplicate of the Excel-created trendline, using the Excel-gene=
rated=20
> > > equation. You now have two trendlines - one on top of the other; how=
ever, on=20
> > > the extract series, all plotted values can be determined - not possib=
le with=20
> > > normal function of Excel.
> > >=20
> > > That's what I want to do in the second example, the 2nd order polynom=
ial=20
> > > trendline, is create a second trendline, where I can access the value=
s of=20
> > > each point on the plotted curve. Do you know how to do this?
> > >=20
> > > As to changing number format, this has no impact on the issue that I =
can=20
> > > rationalize. Can you help me further?
> > >=20
> > > Thanks, Phil
> > >=20
> > > >Tshar Mehta" wrote:
> > >=20
> > > > On the chart, double click the trendline equation, then select the=
=20
> > > > Number tab. Change the format to something that shows lots of deci=
mal=20
> > > > places (scientific with 14 decimal places, for example).
> > > >=20
> > > > --=20
> > > > Regards,
> > > >=20
> > > > Tushar Mehta
> > > > www.tushar-mehta.com
> > > > Excel, PowerPoint, and VBA add-ins, tutorials
> > > > Custom MS Office productivity solutions
> > > >=20
> > > > In article <[email protected]>,=20
> > > > [email protected] says...
> > > > > I am calculating/plotting a log trendline extract on the equation
> > > > > y =3D 533371Ln(x) - 3E+06. Ln(x) is the period (extract) number,=
starting at=20
> > > > > 1 and ending at 288. There are 288 plotted points. The value for=
533=C3=3D3F=C2=A2=C3=A2=3D3F=C2=AC=3D3F and=20
> > > > > 3E=C3=3D3F=C2=A2=C3=A2=3D3F=C2=AC=3D3F are derived from the trend=
line equation each month, when a new value (and=20
> > > > > account balance) is added to the series. The plotted extracted v=
alues (y)=20
> > > > > give me an exact duplicate of the Excel log trendline curve, and,=
a value=20
> > > > > (forcast) for period 288, where the trendline crosses the right s=
ide of the=20
> > > > > chart. The series looks like this, where cell L2 holds the 533 v=
alue, M2=20
> > > > > holds the 3E value, and G* is the extract number. Works fine.
> > > > >=20
> > > > > =3D(L2)*LN(G1)-M2
> > > > > =3D(L2)*LN(G2)-M2 =20
> > > > > =3D(L2)*LN(G3)-M2
> > > > > etc.
> > > > > =3D(L2)*LN(G288)-M2
> > > > >=20
> > > > > Now I want to do a second series, using a 2nd order poly trendlin=
e with the=20
> > > > > equation y =3D 1.8485x2+641.06x (given by Excel). The extract di=
mension=20
> > > > > remains 1 through 288, and the series is:
> > > > >=20
> > > > > =3D1.8485*(G1)+641.06*(G1)
> > > > > =3D1.8485*(G2)+641.06*(G2)
> > > > > =3D1.8485*(G3)+641.06*(G3)
> > > > > etc.
> > > > > =3D1.8485*(G288)+641.06*(G288)
> > > > >=20
> > > > > The final value (G288) is coming up 183,228, when it should be in=
the region=20
> > > > > of 340,000, the approximate value where the Excel-plotted trendli=
ne crosses=20
> > > > > the right side of the chart.
> > > > >=20
> > > > > Where is the logic flaw?
> > > > >=20
> > > > >=20
> > > >=20
> > >=20
> >=20
>=20