Physics 305 Hints: Using LINEST in Excel
Microsoft Excel™ is particularly convenient for
organizing data, manipulating data and ultimately for
displaying graphs of ordered pairs of data. The
trendline feature in Excel makes identifying the “best-
fit line” nearly completely effortless. The equation of
the trendline can be displayed on the chart by clicking
on the “options” menu at the appropriate stage while
developing the graph and then clicking on the box
labeled “display equation on sheet”. Alternatively,
you can right-click [control-click on Macintosh] on the
best fit line on an existing graph and select “Add
Trendline” or “Trendline Options” to access this
feature. Information on the slope and intercept of the
trendline are reported on the finished product. But for
many circumstances in physics, this is not enough!
We often need to also know the uncertainties in these
quantities.
It turns out that Excel has a particularly convenient utility for carrying out such
calculations: A function called LINEST (which stands for LINE STatistics). To access
the uncertainties in the slope and intercept do the following:
1.
Using the mouse, highlight a region of empty
cells that is two columns wide by five rows
tall. This is where you will be inserting the
results from LINEST.
2.
Without deselecting the highlighted region,
go to the formula bar and type the following
=LINEST(ydata,xdata,true,true)
NOTE: “ydata” indicates the cell numbers
corresponding to the range of y data (e.g. – it
might read B2:B10 if you have y data in cells
B2, B3,…,B10). The words “true” ought to
literally appear as true comma true.
The figure to the right shows someone
entering the appropriate line (except for the
final parenthesis).
Showing the result of “right-clicking” on the data in
a
p
lot in Excel to access the Trendline feature.
