Andrew J. Pounds, Ph.D
Departments of Chemistry and Computer Science
Mercer University
Basic video on how to fit a linear line to data using something like a least squares method1) Trial and error method2) Least Squares3) Solver40 Using the 'Ad. Coefficients: These are the coefficients on the variables that describe the line of best fit. In this example, we would assemble the coefficients into the equation: Standard Error: This value tells us how much the observed values deviate from the best-fit line. T Stat: This is the value you would use in a t-test. For a set of data, I need to fit the best curve with the equation of y=x^b; I know excel has the power trendline of y=a.x^b, but I cannot have the 'a' coefficient. In general, is it possible to define our custom trendline in excel, rather than using those six predefined functions?
Best Fit Line Excel 2013
The assumption is that you have some data from your laboratory that you need to graph and that the datafrom your experiment needs to be fit to a best fit line. This is easily accomplished in Excel.Let us assume that you have recorded the average number of bananas consumed per hour for various sizedcages of monkeys. In the diagram below I have entered the data.
I then need to highlight the data with the mouse.
After I have the data selected I go to the top menu and hit the 'Insert' menu and select the 'Chart' submenu. Oncethe chart menu comes up, select the 'XY Scatter' chart type. The screen shouldthen look like what is seen below.
Hit the NEXT button and you will get the screen below.
At this point Excel is going to let me customize my plot by adding a title and axis labels. You should do this at a minimum. Other customizations are also possible but will not be discussed here. I have entered a title and axis labels for the data collected.
After hitting NEXT, I am taken to a screen that asks where the new chart is to be drawn. Select 'As new sheet' as shown in the figure below.
Best Fit Line Excel
Once I have gotten to this point I can hit FINISH. Excel will then draw the chart in a new sheet in the current workbook and placeme on that sheet. It will look something like the screen shot below.
Best Fit Line Excel Mac
Now the task is to add the best fit line. Excel calls this a trendline. To start this process select the 'Chart' menu option and the 'Add Trendline' menu suboption. You will be presented with a selection box that looks like the following. Select the 'Linear' regression type as indicated.
Then select the 'Options' tab.Add any custom title you want for the best fit line, and check the box to Display the equation on the chartas indicated below.
Once you have done this, select 'OK' and the chart, with the associated best fit line should appear on your chart along with the equation for the best fit line.
