Thread: Curve of best fit

Is there a way of creating a curve of best fit using excel 2003 or excel 2007 using the values:

D I
0 132
10 93
20 85
30 51
40 42
50 38
70 27
100 20
150 9
200 9

I have tried creating a scatter graph and then adding a trendline but it did not end up as the result as I had wanted.

Thank you in advance for your help

Yes thank you for your help, i am trying it at the moment.

Sorry, I am finding it too complicated to apply to the set of values I have used. Is there a macro that is able to create a "curve of best fit" for the values that have been supplied?

Thank you again for you help.

Performing curve fitting in Excel is usually pretty easy. The LINEST (and LOGEST function if your not good at the transformation needed to get an exponential curve) function is provided for this purpose.

If the correct equation form is available, you can use the chart trendline utility. With the data series plotted in an XY scatter plot, right click on the data series and select add trendline, select the desired equation type and. If you need to use the coefficients in other caclulations, though, you have to find a way (manually might be easiest) to get them into a spreadsheet. This is why I prefer using the LINEST worksheet function.

Probably the hardest part of doing curve fitting isn't specific to Excel. That's deciding what kind of equation best represents your data. I haven't looked at your data in great detail, but my first impression was an exponential decay model (y=ax^-b) which should be easily obtained using the LOGEST function (see Excel Help for the LINEST and LOGEST functions). If something else is better suited (polynomial or inverse function y=ax^-b), there are ways to get these, too.

I have tried using the LOGEST function. But for some reason, when I try to get "b" I cannot obtain a value. I used the formula "LOGEST(B2:B11,A2:A11,TRUE,FALSE)" and using the F2, crtl, enter ,shift combination. Also I obtained a value of 0.986433385 for "m", but doesn't this mean that the gradient of the curve is increasing?

Also, I tried to obtain a series "line of best fit" values using the formula: "=b*m^x" but i also cannot get any values. I referred to the link http://www.ecr.unimelb.edu.au/~xlr8tr/techs/logest.xls to work out my "line of best fit values".

Thanks everyone for their help so far and any further assistance is very much appreciated.

I was in error in my description of the LOGEST function. It fits to the curve y=bm^x.

Can you be more specific as to the problem you are having? What do you mean you can't get b? I used LOGEST and got an m and a b. It doesn't generate a real good fit of the data, but it does have approximately the correct shape and such. "Goodness of fit" depends on how accurate the data are. I could accept the fit I got if the raw data aren't very accurate.

What do D and I represent? What sort of relationship do you expect between D and I?