Results 1 to 4 of 4
Thread: curve fitting

20120722, 08:39 #1
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,934
 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.engtips.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 addins 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 "addin" 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

20120722, 13:28 #2
 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 squarederror, I prefer using a predicted function. 3 points define a parabola, but why fit a parabola over a line just to reduce the squarederror if the equation does not really "justify it"?
Steve

20120723, 13:28 #3
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,934
 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 insidecar 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 addin 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

20120723, 17:44 #4
 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 "nonlinear regression" (using something like Solver in excel) to fit the curve and asymptote.
Steve