---

Assignment 12:

Excel Explorations

By Bennett Lewis

---

In this assignment, we will use Excel spreadsheets to examine some sample data. We will use Excel 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 one can cut from a tree is related to its age. Below is a table of known value for some ages of trees.

Next we want to use this data fill in the blanks of our data. To do this we must first graph the data points. Their graph is below.

To be able to fill in other values, we must fit a line to this data. There are several types of lines/curves we can use. We will try several to determine which best fits our data. We will do this by looking at the R^2 (R-squared) for each fitted line. R^2 is the correlation coefficient that measures how well the fitted line will predict other values. R^2 can range form 0 to 1 and the better the fit of the line, the closer R^2 will be to 1.

We can see that the polynomial equation fits the line best. Notice it has the R2 value closest to 1. It’s equation and R2 value are

and

Using this equation, we can fill in our table. Using Excel’s ability to type in the formula we can compute all values at once.

Click here for the excel file used in this investigation.

---