Results 1 to 10 of 10
  1. #1
    Star Lounger
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 non-rounded values makes the polynomials coincide.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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 curve-fitting, 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 cube-root 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 "Curve-fitting" 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

  4. #4
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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 "black-box" 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

  6. #6
    Platinum Lounger
    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 "black-box" 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.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Star Lounger
    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 curve-fitted 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.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •