Assignment Twelve: Using Spreadsheets

By: Melissa Wilson

In this assignment, we will use spreadsheets in Excel to look at some sample data. Excel is a program that allows you to investigate data in multiple ways. We will use it to fit an equation to some existing data to be able to interpolate or extrapolate to other points of interest.

In the lumber industry, the amount of board feet that you can get out of a given tree can be related to the age of the tree. Below is the table of known values for some ages of trees.

Next we want to use this data to fill in the table and to also know other values. To do this we will need to first graph the data points. The result is shown below.

To be able to fill in other values we need to fit a line to this data. We will try several different types to determine which fits best. We will do this by look at the R-squared value for each fitted line. R-squared is a correlation coefficient that measures how well the fitted line will predict other values. R-squared can range from 0 to 1 and the better the fit, the closer the R-squared will be to 1.

We can see that the polynomial equation fits the line best visually. It also has the closest R-squared value to 1.

We will use that equation to now fill in the table. Excel makes this easy for us since we can type the formula in once and have it compute all of the points.

Click here for the excel file used in this investigation.


Return to Main Page