Assignment 12: Exploring Mathematics in Spreadsheets

by Shawn Broderick

For this assignment about exploring mathematics using spreadsheets, I chose to investigate problem 8 from the Assignment 12 web page.

Question 8 is about finding some information about how many feet of boards you can get from a tree based on their age. The older the tree the bigger it is and thus, the more board feet you can get out of it. Here is the table given in the problem description:

 Age of Tree Hundreds of Board Feet 20 1 40 6 60 80 33 100 56 120 88 140 160 182 180 200 320

What I will do is use a spreadsheet and (1) fill in the missing numbers using a trendline, (2) extend the table to see how many board feet can come from a 300-year-old tree by extending the trendline, and (3) investigate different types of trendlines to see which is the best.

(1) and (2) Using Excel, I inserted the data into the cells like this:

With Excel, you can make a chart from this data. All you have to do is select the two columns, click the chart button and that's about it! Here is the chart that I made initially:

As you can see, there are only a few data points to work with. When I saw this pattern and previewed the possible types of trendlines, I thought that an power trendline would be the best fit. Here is the added trendline and equation:

I used the equation to set up another table of numbers to interpolate the numbers I needed in between the ones that were there and extrapolate the ones I needed for up to 300 years old. Here is the new table:

 Age of Tree Hundreds of Board Feet (Power Trendline Data) 20 1.0 40 5.8 60 15.9 80 32.5 100 56.7 120 89.3 140 131.1 160 182.9 180 245.3 200 319.0 220 404.5 240 502.5 260 613.4 280 737.9 300 876.3

Now we have filled in the table and extended it!

(3) Finally, I wanted to compare the errors of the various trendlines. So, to the chart that I had made, I added the rest of the trendlines and pulled their equations:

Blue = original data
Black = Power Trendline
Pink = Exponential Trendline
Orange = Linear Trendline
Green = Polynomial Trendline

To check the error of the trendline we take each data point of the original data square the difference from the trendline point and average that set of values. Here is what I found for the errors:

 Power Trendline Error 0.6 Polynomial Trendline Error 8.4 Linear Trendline Error 1306.1 Exponential Trendline Error 14362.9

Here we can see that the closest trendline is the power trendline as we visually assessed in the beginnning. Spreadsheets are a great tool. This is just one application of many.

Here is the Excel file if you were curious to explore it further:

Tree Data Shawn.xls