If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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? |
#10
|
|||
|
|||
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? |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form's datasheet view - sort order problem | Simon Wong | Using Forms | 7 | October 18th, 2004 02:28 PM |
Adding a Record to the Database Out of Sequential Order | Lisa | General Discussion | 3 | September 29th, 2004 04:59 PM |
Adding a Record to a Form Out of Order | Lisa | Using Forms | 3 | September 29th, 2004 04:46 PM |
Inhereting the sort order | Dan St.John | Setting Up & Running Reports | 1 | July 9th, 2004 03:30 AM |
X Axis Sort Order | Jason | Charts and Charting | 2 | October 9th, 2003 04:12 PM |