4th order curve fitting issue

[gerryucd]

Hi this is my first post on this site and would appreciate some help,

I am doing data analysis of instrumented laterally loaded pile tests, long story short, I have bending moment (y axis) depth below ground level (x axis) curves. I have fitted these data curves with a 4th order polynomial curve. I then use the LINST function to give me the equation of the line I.E. the bending moment equation y= ax4 + bx3 + cx2 +dx + e this equation is then later differentiated and integrated as part of the further analysis.

My issue is, excel appears to just fit a curve with the lowest R2 value or the mathematical best fit (am I correct??). Is it possible to use the solver function to give me alternative fitted curves? Obtaining the lowest R2 value is not priority here it’s more the shape of the line so I would like to use solver to fit a curve and specify that it will have y= + ax4 – b x3 –cx3 +d x +e??
Thanks
Gerry

 

[IDS]

I don't follow. If you set up Linest to fit a 4th order polynomial Excel will find the best fit 4th order polynomial line; i.e. the line with the lowest R2. Why would you want a different shape, and what constraints would you give Solver to find some different shape?

Linest does sometimes have some problem with higher order curves, but these should not be an issue with fourth order.

You might find my blog article here useful:

http://newtonexcelbach.wordpress.com/2011/01/19/using-linest-for-non-linear-curve-fitting/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

It's quite possible that a 4th order polynomial will not give a good fit though. You might well be better off with a cubic spline.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[gerryucd]

Why would you want a different shape, and what constraints would you give Solver to find some different shape?
I have several test piles and the 4th order equation is working well for most of the results, however I have noticed problems further in the analysis if the equation does not follow the sign convention y= + ax4 – b x3 –cx3 +d x +e. What I am hoping to determine is the best fit line equation if the signs have to be as in the above equation. I have not experience with solver so want to know if setting this up is possible.

It's quite possible that a 4th order polynomial will not give a good fit though. You might well be better off with a cubic spline.
One of my colleagues has investigated using cubic spline curves but found them only to be marginally more suitable as they eventually lead to the same problems. This is a long shot really as I want to see the effect of manipulating the moment curve on the differentiated results

 

[Latexman]

I don't understand. If the best fit says + b = 1234, then changing the format and doing a best fit should say - b = - 1234. Right?

Good luck,
Latexman

 

[IDS]

One of my colleagues has investigated using cubic spline curves but found them only to be marginally more suitable as they eventually lead to the same problems

I'm still not clear what the problem is, or why having signs for the coefficients different from those indicated would be a problem. In fact the deflections that come out of a continuous beam analysis, with loads applied as point loads, is exactly a cubic spline, so this would seem to be the best approach for what you want to do.

That said, if you want to use solver and specify that a, d and e will always be positive, and b and c always negative, then yes you can do that. You would need to set up a table to calculate r2 for any specified value of a, b, c, d, and e, and get solver to minimise r2 with whatever constraints on the sign or magnitude of the coefficients that you want.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[gerryucd]

I was hoping to do something like that. Can you tell me more about setting that up as though I work a lot with excel I have not yet used the solver add-in and I am under a tight deadline for exploring this possiblity.

Thank you very much, sorry if my explinations are not very clear.

 

[surfer200]

Read this article from Tushar.Metha site, about this theme

http://tushar-mehta.com/publish_train/data_analysis/16.htm

 

[jhardy1]

My $0.02 worth:

You can fit any number of curve types to any set of data. If you are using polynomial curve fitting, in general, increasing the polynomial order will improve r^2 - but this doesn't mean that a high order polynomial is the "best" curve fit to your data. You need to think about what it is you are analysing, and what underlying relationship you expect to find.

For example - if your data relates to a population of rabbits in a field with a good food supply, you would probably expect some sort of exponential growth (at least initially, before the population exceeds the available food supply). There is no obvious reason to expect a population of rabbits to follow a cubic or fourth-order relationship. If your data relates to free vibrations of a structure, then a sinusoidal function might be expected.

If your data relates to bending in a beam with concentrated loads, then classic beam bending theory says the deflections should follow a third-order (cubic) curve. If the beam is subject to uniform distributed loads, the same theory says the deflections should follow a fourth-order curve. Note that both the third-order and fourth-order curve-fits will generally fit the actual deflections "very well", with r^2 approaching 1.0 in both cases, but only one curve-fit relationship is "correct" in each case.

Think about your problem and your data, and then choose the most appropriate curve fit function - even if it means you get a larger r^2 than some other curve-fit function.

http://julianh72.blogspot.com

 

[IRstuff]

You need to pick the lowest order that still gives a plausible answer. If you had 100 points, you could get a perfect fit with 100 control points, but it would have no connection to reality. Whatever you're measuring has limited degress of freedom. This is generally why fitting requires full understanding of the physics and mechanics of a problem. A simple bending beam should not be modeled with, say, a 6th order polynomial or spline, because the physics does not support such a fit. To wit, -3, 0, +3, 0, +2, -1, -2, +1 could be perfectly fitted with a high-order polynomial, which could be right if that's what the physics looks like. However, it could simply be a straight line measured by a really noisy ruler, in which case, the high-order polynomial is completely wrong.

You may need to really try to analytically model the problem; perhaps a polynomial or spline is completely inappropriate for the actual physics.

TTFN
faq731-376

 

[IDS]

You may need to really try to analytically model the problem; perhaps a polynomial or spline is completely inappropriate for the actual physics.

Whilst that's true in general we know what the physics of a laterally loaded pile is, and that a cubic spline will give a good fit. An unconstrained 4th order polynomial should give a pretty good fit as well. I have just generated a bending moment curve for a laterally loaded pile, and fitted a 4th order polynomial using Linest and also with Solver with no constraints and with the coefficients constrained to be +ve and -ve as indicated by gerryucd (see attached file). The Solver procedure is:

- Assume some values for the coefficients, a to e.
- Generate the curve defined by these coefficients
- Calculate the sum of the squares of the error at each point, that is (calculated value - actual value)^2
- Minimise that value using Solver

The results of the analysis show that:

- The Linest solution gives a good fit to the moment curve (certainly good enough for practical purposes)
- The unconstrained Solver solution is almost identical
- The constrained Solver solution is significantly different, and a much poorer fit to the data.

I suggest that if the unconstrained Linest fit is causing problems with later analysis there is something wrong, either in the later analysis procedures, or in the curve fitting procedure.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[missil3]

sorry dint read the entire thread but try curve fitting with this site:

http://www.zunzun.com

. Its an amazing tool for curve fitting, you can choose what method it chooses as well (least square method ,etc).