Curve-fitting

[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?

 

[Gary''s Student]

Start here:

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

 

[Paul Hyett]

Start here:

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

Thanks. I'll have a look at Log, Power & Exponential - though in the
past none have provided that close a fit to my data.

 

[Mike Middleton]

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]

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.

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

Thanks!

 

[Lori Miller]

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.)

 

[Paul Hyett]

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

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.)

I'll experiment - thanks.