4th order curve fitting issue
4th order curve fitting issue
(OP)
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
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





RE: 4th order curve fitting issue
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/us...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: 4th order curve fitting issue
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: 4th order curve fitting issue
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
RE: 4th order curve fitting issue
Good luck,
Latexman
RE: 4th order curve fitting issue
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/
RE: 4th order curve fitting issue
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.
RE: 4th order curve fitting issue
http://tushar-mehta.com/publish_train/data_analysi...
RE: 4th order curve fitting issue
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
RE: 4th order curve fitting issue
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: Eng-Tips.com Forum Policies
RE: 4th order curve fitting issue
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/
RE: 4th order curve fitting issue