Line of best fit in Excel

[-Alex-]

Right, so I have biology coursework to do and i've decided to do it on the PC rather than by hand for reasons most people know. Now, the graph that we have all drawn by hand looks mostly like the scatter graph that Excel has generated for me, given this set of results for our experiment:

But, for full marks, it needs to be a line of best fit, whereas the graph that Excel has made is a simple line join:

I am using Excel 2007 Beta 2. Any ideas guys?

Thanks a lot,

Alex.

Edited October 29, 2006 by Alex-Jones

[Qumahlin]

I think this may be what your looking for.

1. Be sure you are on the worksheet which contains the chart you wish to work with.

2. Move the mouse cursor to any data point and press the left mouse button. All of the data points should now be highlighted. Now, while the mouse cursor is still on any one of the highlighted data points, press the right mouse button, and click on Add Trendline from the menu that appears.

3. From within the "Trendline" window, click on the box with the type of fit you want (e.g., Linear).

4. Click on Options at the top of the "Trendline" window.

5. Click in the checkbox next to "Display Equation on Chart" and the checkbox next to "Display R-squared Value on Chart". Do not click on the checkbox next to "Set Intercept = 0".

6. Click OK. A line, an equation, and an R-squared value should appear on the graph

[+BudMan MVC]

Right click on your series, and pick add trendline

you can then pick linear, log, polynomial, moving avg

[Shetland]

ouch reminds me of my old rate of reaction coursework.

[-Alex-]

That works to an extent, but the only one that looks remotely correct is polynominal of order 4, and that doesn't go through the top point:

[Dick Montage]

A line of best fit should't go through all poits, don't worry!

[-Alex-]

I know that much, but is there a way to force it to go through the top point? I don't see why that should be missed out..

[Slimy]

What you have there is a line of best fit, if you force it to go through the top point it will defy the whole purpose of it.

[bionixxx]

That shows that the top result is anonymous, like slimy said, that's what the line of best fit is there to prove ;)

[-Alex-]

I think you mean anomalous; and it's not anomalous at all. If any is anomalous it is the reading for 55???C.

[Minchino]

Then maybe you should consider ignoring the anomalous result, remove it from the graph and get a line of best fit closer to the top.

[SquaredNull]

Well, I guess it depends on hypothesis. With completely unpredictable results (that ones you cannot explain from your theoretical estimates) simple line plot would be acceptable. But if you have some extimations you can use moving average estimator (what Excel does) or can use spline interpolation (you need scientific packages like MATLAB, Maple, Mathematica or MathCAD). Chosing high-order polinomials for trending data can lead to reverse improper results.

[MiG-]

I know that much, but is there a way to force it to go through the top point? I don't see why that should be missed out..

because it looks like an anomaly (spelling ?) to me..

EDIT - i think 50 - 55 is some wat anonamous.

[Deepu Sudhakar]

The regression techniques are doing they are supposed to do...not all the data points will fall on the trend line. If you think the trend line is to low, the only way to "manually" adjust it is to give it altered values for the regression. Remove the anomalous 55 degree one. That will definitely adjust that fourth order polynomial to be closer to the maxima.

[SquaredNull]

In this particular case with anomalous point the best way is to simply exclude it from the set as suggested earlier. Or just move the plotted line the way it should go from your personal guess-works (just drag the plotted curve and Excel will automatically change the data point value). Then you can plot the trend again.

With more complex case you need to do best-fit analysis. The simple example can be found here.

If you need to proceed such tasks frequently I recommed to look at Curve Fitting toolbox.

Of course, the higher the polynom order the closer it goes to the points but it doesn't matter that a*x^256+x^12+b fits best than x^3+1293x+c because the first one will go the false way.

[-Alex-]

because it looks like an anomaly (spelling ?) to me..

EDIT - i think 50 - 55 is some wat anonamous.

The top result is not an anomoly as it is the best temperature for protease to digest protein at. However, 55 is. I will exclude it in the graph, thanks guys. :)

[Echilon]

I'm pretty sure you're only supposed to use excel graphs to illustrate. We always needed hand drawn graphs for full marks.