8th Order Polynomial Trendline - Is there an easy way?

Excel can handle polynomial trendlines up to 6th order through the Add
Trendline command. I would like to be able to add a trendline up to the 8th
order. Google searches showed me how to compute this, but I am wondering if
there is an easier way. I tried using VBA :

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlPolynomial,
Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:=
_
False).Select

and then changing the Order:=8 but it would not work.

Any great ideas?

Thanks,

Frank

Use LINEST to generate coefficients - see
www.stfx.ca/people/bliengme/ExcelTips
Use the coefficients to generate trendline data

Do your really have data that can meaningfully be fitted to 8th order?

Best wishes

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"Frank & Pam Hayes" wrote in message
newsw89d.12192$na.6170@trnddc04...
Excel can handle polynomial trendlines up to 6th order through the Add
Trendline command. I would like to be able to add a trendline up to the
8th
order. Google searches showed me how to compute this, but I am wondering
if
there is an easier way. I tried using VBA :

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlPolynomial,
Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select

and then changing the Order:=8 but it would not work.

Any great ideas?

Thanks,

Frank

Bernard -

"Do your really have data that can meaningfully be fitted to 8th order?"

This was my first thought. I've found most higher than third order are ill-advised.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Bernard Liengme wrote:

Use LINEST to generate coefficients - see
www.stfx.ca/people/bliengme/ExcelTips
Use the coefficients to generate trendline data

Do your really have data that can meaningfully be fitted to 8th order?

Best wishes

Thank you both for your comments. Let me tell you what I am am doing and
ask your advice. I am constructing a cumulative probability curve of
values, bounded by 0% and 100% probabilities. The curve generally follows a
basic S shape with the very low probabilies and very high probabilities
going asyntotic. It is very important that the trendline does not go below
0% or above 100%.

Here are the data I am using:

X-values (Expected NPV)

(123.981)
(101.476)
(85.174)
(68.565)
(68.002)
(50.994)
(41.568)
(39.868)
(34.903)
(33.363)
(32.627)
(22.587)
(21.014)
(10.907)
(9.361)
(8.694)
(5.820)
(5.659)
(2.764)
0.829
1.342
3.878
5.467
6.090
8.851
10.436
10.795
11.237
13.438
14.695
17.310
19.057
19.765
21.193
21.666
22.925
25.311
34.417
35.560
36.511
38.089
38.796
42.145
44.587
56.408
58.991
59.794
61.564
61.920
66.724
71.064
74.772
77.386
79.294
85.779
89.446
93.157
93.761
93.858
98.944
99.150
99.998
101.652
117.539
129.741
191.151
206.613
219.735
220.505
255.970
256.805
260.091
279.759
289.067
303.237
308.903
312.172
362.389
366.679
384.683
438.860


Y-Values (Cumulative Probability)
0.008
0.023
0.027
0.043
0.051
0.066
0.070
0.078
0.086
0.094
0.102
0.133
0.141
0.156
0.172
0.180
0.195
0.199
0.203
0.234
0.250
0.258
0.262
0.270
0.277
0.293
0.301
0.305
0.309
0.313
0.320
0.336
0.340
0.344
0.352
0.355
0.363
0.371
0.379
0.395
0.398
0.406
0.410
0.418
0.434
0.449
0.457
0.465
0.473
0.480
0.496
0.500
0.508
0.512
0.520
0.527
0.543
0.574
0.582
0.598
0.613
0.645
0.652
0.668
0.684
0.699
0.703
0.711
0.719
0.750
0.781
0.797
0.805
0.813
0.828
0.844
0.859
0.922
0.953
0.969
1.000


Thank you to both of you. I have used much of your work in my own meager
efforts at mastering excel.

Frank


"Jon Peltier" wrote in message
...
Bernard -

"Do your really have data that can meaningfully be fitted to 8th order?"

This was my first thought. I've found most higher than third order are
ill-advised.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Bernard Liengme wrote:

Use LINEST to generate coefficients - see
www.stfx.ca/people/bliengme/ExcelTips
Use the coefficients to generate trendline data

Do your really have data that can meaningfully be fitted to 8th order?

Best wishes

Frank -

... Let me tell you what I am am doing and ask your advice. I am
constructing a cumulative probability curve of values, bounded by 0% and
100% probabilities. The curve generally follows a basic S shape with the
very low probabilies and very high probabilities going asyntotic. It is
very important that the trendline does not go below 0% or above 100%.

My advice would depend on what you plan to do with the cumulative curve.

For example, if you simply want to display it, then you could add an
arbitrary point for the 0% cumulative probability, plot an XY (Scatter)
chart, and use Format Data Series | Patterns | Line | Smoothed Line.

I don't know where your cumulative probabilities came from, but if your
original data were discrete probabilities, you could display the cumulative
distribution using a stair-step pattern. I think Tushar Mehta has an example
on his Step Chart page at www.tushar-mehta.com.

- Mike

www.mikemiddleton.com

And if the data can meaningfully be fitted to an 8th order polynomial, I
would still worry about numerical problems unless you were using Excel
2003 and no coefficients were estimated to be exactly zero
http://groups.google.com/groups?selm...0no_e-mail.com

Jerry

Bernard Liengme wrote:

Use LINEST to generate coefficients - see
www.stfx.ca/people/bliengme/ExcelTips
Use the coefficients to generate trendline data

Do your really have data that can meaningfully be fitted to 8th order?

Complementing Mike's suggestion about a step chart (the specific address
is http://www.tushar-mehta.com/excel/ch...art/index.html), you
may also want to consider stepwise-linear interpolation to estimate
probability values for X0 = X - Xn. For one approach see
Interactive Chart
http://www.tushar-
mehta.com/excel/software/interactive_chart_display/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004

In article G_B9d.412$275.97@trndny01, says...
0.008
0.023
0.027
0.043
0.051
0.066
0.070
0.078
0.086
0.094
0.102
0.133
0.141
0.156
0.172
0.180
0.195
0.199
0.203
0.234
0.250
0.258
0.262
0.270
0.277
0.293
0.301
0.305
0.309
0.313
0.320
0.336
0.340
0.344
0.352
0.355
0.363
0.371
0.379
0.395
0.398
0.406
0.410
0.418
0.434
0.449
0.457
0.465
0.473
0.480

In message G_B9d.412$275.97@trndny01, Frank & Pam Hayes
writes
Thank you both for your comments. Let me tell you what I am am doing and
ask your advice. I am constructing a cumulative probability curve of
values, bounded by 0% and 100% probabilities. The curve generally follows a
basic S shape with the very low probabilies and very high probabilities
going asyntotic. It is very important that the trendline does not go below
0% or above 100%.

You are dead in the water with a polynomial fit then. The higher the
order of polynomial will by its nature make wild excursions in the
nether regions.

Your best bet would be to fit a function that necessarily by
construction matches your prescribed boundary conditions as x tends to
infinity.

A scaled version of (1 + tanh(Bx))/2 might be worth a try.
Where B is to be determined by least squares fitting.

Regards,
--
Martin Brown

Another idea:
Generalized inverse Tukey-lambda fit, which requires but 4 parameters, and
is very well behaved at endpoints. The fit is on the inverse cumulative, and
seems to be very stable wrt Excel.

"Jerry W. Lewis" wrote in message
...
And if the data can meaningfully be fitted to an 8th order polynomial, I
would still worry about numerical problems unless you were using Excel
2003 and no coefficients were estimated to be exactly zero
http://groups.google.com/groups?selm...0no_e-mail.com

Jerry

Bernard Liengme wrote:

Use LINEST to generate coefficients - see
www.stfx.ca/people/bliengme/ExcelTips
Use the coefficients to generate trendline data

Do your really have data that can meaningfully be fitted to 8th order?

David,

The Tukey-lambda fit looks like it has promise for my cumulative probability
curve, but a google search on Tukey-lambda and Excel was pretty sparse.
Searching on Tukey-lambda alone brought many more results, most of which
were beyond my statistical competance. The cumulative distribution function
shown at : http://www.itl.nist.gov/div898/handb...n3/eda366f.htm
looks to be exactly what I am trying to produce.

Can you point me in the right direction on how I would use Tukey-lambda in
Excel to calculate the cumulative probabilty curve?

Frank

"David J. Braden" wrote in message
...
Another idea:
Generalized inverse Tukey-lambda fit, which requires but 4 parameters, and
is very well behaved at endpoints. The fit is on the inverse cumulative,
and seems to be very stable wrt Excel.

"Jerry W. Lewis" wrote in message
...
And if the data can meaningfully be fitted to an 8th order polynomial, I
would still worry about numerical problems unless you were using Excel
2003 and no coefficients were estimated to be exactly zero
http://groups.google.com/groups?selm...0no_e-mail.com

Jerry

Bernard Liengme wrote:

Use LINEST to generate coefficients - see
www.stfx.ca/people/bliengme/ExcelTips
Use the coefficients to generate trendline data

Do your really have data that can meaningfully be fitted to 8th order?