Data analysis in Excel

Linear relationships are characterized by a slope (m) which tells the steepness of the line and an intercept (b) which tells the value of Y when x is zero.  Because of random measurement errors, experimental data will seldom fall exactly on a line given by the equation Y = m x + b.  A pertinent question is: What is the best slope and intercept to describe our linearly correlated but imprecise experimental data?

When it is important to make the best possible use of the data, the fitting must be done numerically.  The computational method most commonly used is the method of least squares.  The best linear line is the one that minimizes the distance of all the data points from the line.  That is the line which minimizes the squared difference (Y – yi)2 between the observed data points yi and the calculated data points Y = m xi + b.  Such a line is called a linear least squares line or a linear regression line.

The method of least squares is built into many computer programs for analyzing data.  We will be using Excel to analyze our data this semester.

Plotting and linear regression can be done in Excel. In one column of cells (let’s say column A starting with row 1), enter your x-data. In the cells of the neighboring column (B), enter the corresponding y-data. Select the data you entered. Then from the top menu, select Insert –> Chart –> X Y (scatter). Select the points on the graph, right click, and select “Add trendline”. Make sure the trendline options are set to “Linear” with the equation and R-squared value displayed on the chart.

Dressing up the figure:  You need axis labels.  Under Layout, click Axis Title and then “Primary Horizontal Axis Title” and finally “Title Below Axis”. Type in an appropriate title with appropriate units.  Do the same for the Primary Vertical Axis Title…used “Rotated Title”. You want your graph to be as large as possible, and you don’t really need the box on the right side that says Series 1 and Linear. Click this and delete it. Gridlines aren’t normally placed on a figure, so click on one of them and delete them. If your equation line and R2 value landed on top of the line, click on that information and move the box to a good location. Save this file.

There is error in the linear regression data that will determine how many significant figures you can report.  The linearity of the line is shown by the R2 value. If you got above 0.98, pat yourself on the back or thank your lucky stars that you have the world’s best lab partner.

Leave a Reply