Results 1 to 4 of 4

Thread: curve fitting

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    curve fitting

    Hi All,

    I have some data that looks like it fits a log function (when graphed with a log scale, it looks close to a straight line). I'd like to fit a log function to this data.

    I searched for curve fitting and Excel (2003) and found lots of stuff. I was not interested in any trials and certainly not in buying anything outright. I did find one site (http://www.eng-tips.com/viewthread.cfm?qid=184726) that gave a bunch of formulas to use for different types of functions. I thought I followed the rules (using LINEST with the 2nd arg as the log of the observed output values) but the resulting values from the fitted function are nowhere near the observed values.


    Any experiences with curve fitting add-ins or otherwise for Excel 2003? It would be even better if you did not need to know what kind of function you need to fit and the "add-in" would fit several and give the least squared error for each fit. I haven't played with LINEST much so not really sure of what it's capable of (although the above site seemed to do everything with it).

    TIA

    Fred

  2. #2
    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
    I am not sure of the difference you see in the Linest with log vs the observed. Did you use the same log (both base e or base 10)?

    Linest is very generic and can calculate any multiple linear regression. As the equations in your reference indicate, some need to be "linearized" to make them work. I don't recommend fitting many equations and picking the best squared-error, I prefer using a predicted function. 3 points define a parabola, but why fit a parabola over a line just to reduce the squared-error if the equation does not really "justify it"?

    Steve

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Steve,

    Thanks. Used both log for LINEST to find the fit and log to get predicted values. I get negative predicted values whereas the data is positive. Same result using ln for both with different negative predicted values. But other than that, if I add a constant of about 160 to the predicted values, it doesn't look bad.

    The data is something I saw somewhere dealing with #minutes spent in a car vs inside-car temp after car has been turned off. Starting temp is 80 F at 0 mins (not sure if there's a relationship of twice the starting temp of 80 and the 160 constant to add to the data to make a reasonable fit) and get readings every 10 mins up to an hour. I graphed the data with a log output scale and it was nearly a straight line. Didn't try an ln output scale but that wouldn't matter.

    As far as choosing parabola vs line, I guess it depends on which side of the vertex the data falls: if it all falls on one side, a line might very well be better. Still I'd like to be able to see the results for a few different kind of curves without having to pick which one to use before. In the above case, lacking the desired add-in or whatever, I graphed the data to be sure and have been playing with a log fit since. I'd take something if it let me choose various curves and gave me the least square errors. Not having worked with LINEST much, it wasn't obvious how to use it for my needs but the site I found seemed to be "LINEST friendly" - just gave me those neg values.

    Fred

  4. #4
    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
    One thing to keep in mind for log functions is that log functions approach 0 as the x approaches infinity. If you are approaching an asymptote that is NOT 0 (as you get when temperatures are approaching some ambient conditions when not in space) then you either must define the asymptote or use some type of "non-linear regression" (using something like Solver in excel) to fit the curve and asymptote.

    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
  •