Results 1 to 10 of 10
Thread: Curve Fit Problem (97SR1)

20031022, 23:04 #1
 Join Date
 Feb 2001
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Curve Fit Problem (97SR1)
Hello,
I have encountered a problem when I was trying to curve fit XY dataset in Excel. For the dataset that I have, for example radius vs. length, I succeeded to add a trendline with a 5th order polynomial. I had the equation of the curve fit as well as its R2 displayed. I then used the displayed equation as part of a new equation to represent radius vs. length. When I plotted this relationship, I obtained a different curve. I have no I idea why this is the case?
I have attached the spreadsheet showing this phenomenon.
Thanks in advance for your help.
Hanan.

20031023, 07:37 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Curve Fit Problem (97SR1)
Rounding errors. Regression yields a polynomial with these coefficients:
0.000185378 * x^5 + 0.003504786 * x^4  0.026354244 * x^3 + 0.099262611 * x^2  0.194576076 * x + 0.228415508
The equation as shown in the chart rounds the coefficients to 4 decimals. Using the nonrounded values makes the polynomials coincide.

20031023, 11:08 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Curve Fit Problem (97SR1)
I agree with Hans and make the further comment:
This is one of the downfalls of not watching significant figures
Going beyond your question, I will raise another question: why did you chose this "model" to fit your data?
This type of fit will get better and better as you add more coeffients and lose your "degrees of freedom". For curvefitting, it might be fine, but for any "science" it leaves a lot to be desired.
What does the data represent? A different (read "simpler" model) might be more appropriate.
I found (for example) that plotting the inverse of the radius vs the cuberoot of the length yields a straight line, so a good representation of length might be:
=(0.122787/radius +0.359512)^3
I just "found" this model thru trial and error. An undertanding of the process where these numbers were generated could lead to a "truer" (ie based on scientific principals) relationship. (some are linear, some inverse functions, some are log functions, etc). These types of models can lead to prediction, hypotheses, and further testing. Simple "Curvefitting" has none of these bonuses.
I always like to keep in mind George Box (a design of experiments expert) who said "All models are wrong, some
models are useful".
Steve

20031023, 11:16 #4
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Curve Fit Problem (97SR1)
I'd say "All models are thin, some are handsome" <vbg>
Have you ever tried this package : http://www.systat.com/products/TableCurve2D/Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20031023, 12:10 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Curve Fit Problem (97SR1)
Nope, never tried it. We use excel and JMP for our designs. I generally do not try to make up a relationship (like in this example) if I have some underlying "theory", I just wanted to bring it up to the poster. (for example: I plot viscosity vs temperature and I always fit a log Visc verus inverse of absolute temperature, since that is theory and it seems to work reasonably well., or use log for first order kinetics, etc).
Excel has many "neat things" that are simple to use, but whose "appropriateness" is left to users, who (in my opinion) far too frequently, use incorrectly or never test the appropriateness
With a Design of Expt (DoE) you kind of use the "blackbox" approach. "I don't have to know any science": the process is a black box with Knobs. Move the knobs see the results, fit a model. Much more important in this scenario to not move outside the limits of experiments since the model fits data not science.
Thanks for the link.
Steve
Steve

20031023, 12:34 #6
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Curve Fit Problem (97SR1)
<hr>With a Design of Expt (DoE) you kind of use the "blackbox" approach. "I don't have to know any science": the process is a black box with Knobs. Move the knobs see the results, fit a model. Much more important in this scenario to not move outside the limits of experiments since the model fits data not science<hr>That may be true, but in many processes the underlying physical model is either not known or really too complex. Also, fitting the results within the region of experimentation with relatively simple linear equations very often suffices.
One of the FIRST things to do when building a designed set of experiments is to take into account all knowledge about the system at hand. This includes the underlying process and physics/chemistry.
Of course when there is just a few parameters to take into account, trying to fit with an equation that at least approaches a physically correct model is the best way to go about it.
IMO much greater mistakes are being made by trying to fit a physically correct model to the wrong variables using the wrong set of experiments then by doing a properly designed set of experiments where all expected interactions between variables may be validated and quantified versus a (maybe too) simple linear model.
Of course using a good DoE package to do the analysis will help. One should not forget to perform the proper variable transformations to account for the underlying processes.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20031023, 12:57 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Curve Fit Problem (97SR1)
I agree with all your points.
Steve

20031023, 13:06 #8
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Curve Fit Problem (97SR1)
<hr>I agree with all your points<hr><img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20031023, 13:10 #9
 Join Date
 Feb 2001
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Curve Fit Problem (97SR1)
Thank you all for the great help and comments. I appreciate it.
In my case, I am limited by time constraints and therefore have to resort to the black box approach of fitting a curve that will go through most of the points. I do however check the validity of the curvefitted equation by enabling the option to forecast about one unit forward and backward in order to determine whether the trendline equation is appropriate beyond the dataset (to a certain limit of course).
I have two additional question though.
1) How do you enable the equation display to allow you to see more significant digits?
2) The equation of [(0.122787/radius+0.359512)^3] is interesting and is not available in Excel. Would you be able to tell me how did you came up with this relationship?
Thanks, Hanan.

20031023, 14:06 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Curve Fit Problem (97SR1)
1) select the equation and then format  selected data label  number tab (or just click the "increase decimal" button)
2) By trial and error I changed the relationship between radius and length. (sqr root, cube root, inverse (1/), log, squared, etc) until it was a straight line. Then I did a slope/intercept of this "transformed data" I then converted back.
That is I noticed that cube root of length [= length ^ (1/3)] when plotted versus the inverse of radius (1/radius) was straight line. That straight line had the equation using slope/intercept functions
y = 0.122787 * x + 0.359512
Since the y is length^(1/3) and x was 1/radius
length^(1/3) = 0.122787 * (1/radius) + 0.359512
which is:
length^(1/3) = 0.122787 / radius + 0.359512
which yields:
length = (0.122787 / radius + 0.359512)^3
I played some more and another good one is
length = (0.175562 / sqrt(radius) + 0.153748)^2
since sqrt of length vs inverse of sqrt of radius is also a straight line
Steve