Skip to main content
N
NeedToKnow1-Visitor
1-Visitor
October 29, 2014
Solved

Curve fitting using straight lines.

  • October 29, 2014
  • 3 replies
  • 2065 views

How to execute Minimize() function to get the slope and intercept for the straight lines?

Best answer by Werner_E
Loi Cheng Poon wrote:

How to execute Minimize() function to get the slope and intercept for the straight lines?

You have not shown in your sheet what you tried to get the optimal parameters, so I don't know if you used minimize() in an incorrect way or you simply struggled because Minimize() won't change any of the parameters.

The latter case happens when you try to minize in your sheet - minize would spit out the guess values without changing any. The reson for this are the huge x values. The numeric algorith would change them slighty(!!) but the ErrFunc will shoe no noticeable change within the given tolerance value. So minimize() decides that the fit is already good enough. Decreasing the value of TOL (or even CTOL ?) does not help in your case.

So one way out is to scale your data (multiply every x value by 10^-9 and of course you would have to multiply ever slope guess by 10^9). Doing so enables minimize() to find a better fit and decreasing the value of TOL even gives you a slight improvement. You may now multiply the slopes found by 10^-9 to get the appropriate values for your data.

I also tried an approach using minerr() (again with the scaled data) but it takes a bit more time to calculate and the fit is slightly worse. Changing TOL or CTOL does not change the result here.

1.png

3 replies

F
Fred_Kohlhepp23-Emerald I
23-Emerald I
October 29, 2014

Sort your data into the ranges you want to fit. Then the functions "line", or "slope", and "intercept" will do a leaast squares fit.

Werner_E
Werner_E25-Diamond I
25-Diamond I
October 29, 2014

Fred Kohlhepp wrote:

Sort your data into the ranges you want to fit. Then the functions "line", or "slope", and "intercept" will do a leaast squares fit.

I guess that this is not exactly what Loi Cheng Poon is looking for. When using the built in linear regression you would have to split the x range in three parts yourself and you don't know where the optimal two points for splitting are. Furthermore the three line segements would not be contiguous and it seems to me from the example provided that this is mandatory.

Werner_E
Werner_E25-Diamond IAnswer
25-Diamond I
October 29, 2014
Loi Cheng Poon wrote:

How to execute Minimize() function to get the slope and intercept for the straight lines?

You have not shown in your sheet what you tried to get the optimal parameters, so I don't know if you used minimize() in an incorrect way or you simply struggled because Minimize() won't change any of the parameters.

The latter case happens when you try to minize in your sheet - minize would spit out the guess values without changing any. The reson for this are the huge x values. The numeric algorith would change them slighty(!!) but the ErrFunc will shoe no noticeable change within the given tolerance value. So minimize() decides that the fit is already good enough. Decreasing the value of TOL (or even CTOL ?) does not help in your case.

So one way out is to scale your data (multiply every x value by 10^-9 and of course you would have to multiply ever slope guess by 10^9). Doing so enables minimize() to find a better fit and decreasing the value of TOL even gives you a slight improvement. You may now multiply the slopes found by 10^-9 to get the appropriate values for your data.

I also tried an approach using minerr() (again with the scaled data) but it takes a bit more time to calculate and the fit is slightly worse. Changing TOL or CTOL does not change the result here.

1.png

Terms & ConditionsAccessibility statement