Curve-fitting

  • Thread starter Thread starter Paul Hyett
  • Start date Start date
P

Paul Hyett

I have a graph that I'd like to fit a curve to, but the 'trend-line'
function doesn't seem to get close.

Does anyone know of a spreadsheet that'll calculate a curve equation
from a given set of data?
 
Paul Hyett -

Your question is much too general.

For your specific situation, you are more likely to get an appropriate reply
if you (a) provide the data, (b) describe the pattern of the "average
relationship," and/or (c) explain the underlying physical phenomenon that is
being modeled.

If the trendline forms are not appropriate for your data, there are other
functional forms that can be fitted using Solver to search for the parameter
values.

- Mike

http://www.MikeMiddleton.com
 
Paul Hyett -

Your question is much too general.

For your specific situation, you are more likely to get an appropriate reply
if you (a) provide the data, (b) describe the pattern of the "average
relationship," and/or (c) explain the underlying physical phenomenon that is
being modeled.

If the trendline forms are not appropriate for your data, there are other
functional forms that can be fitted using Solver to search for the parameter
values.
Click to expand...

Solver - now why didn't I think of that?

Thanks!
 
Try the "smoothed-line" charting option - this looks like the shape a
flexible strip passing through your points could take up. A link to the basic
formula is here:
http://www.mvps.org/directx/articles/catmull/

If you have data in A2:B10 and an x-value in E2, enter this formula for the
y-value:

=SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2;1,-3,3,-1},
OFFSET($A$2:$A$10,MATCH(E2,$A$2:$A$10)-2,0,4,1)-E2
)))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},
OFFSET($B$2:$B$10,MATCH(E2,$A$2:$A$10)-2,0,4,1)))/2

To get values in the first and last data intervals, extend your data range
by one row at each end - you can do this simply by selecting A2:B3 and
dragging the fill handle up to row 1 and also selecting A9:B10 and dragging
down to row 11.

(Adjust the ranges to suit your data and fill down for more data values.)
 
Try the "smoothed-line" charting option - this looks like the shape a
flexible strip passing through your points could take up. A link to the basic
formula is here:
http://www.mvps.org/directx/articles/catmull/

If you have data in A2:B10 and an x-value in E2, enter this formula for the
y-value:

=SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2;1,-3,3,-1},
OFFSET($A$2:$A$10,MATCH(E2,$A$2:$A$10)-2,0,4,1)-E2
)))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},
OFFSET($B$2:$B$10,MATCH(E2,$A$2:$A$10)-2,0,4,1)))/2
Click to expand...

Wow, that's quite a formula. :)
To get values in the first and last data intervals, extend your data range
by one row at each end - you can do this simply by selecting A2:B3 and
dragging the fill handle up to row 1 and also selecting A9:B10 and dragging
down to row 11.

(Adjust the ranges to suit your data and fill down for more data values.)
Click to expand...

I'll experiment - thanks.
 
Back
Top