Use Excel to Fill in Data

By Paulo Tan

In this problem we are given the following information:

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

Notice that there is missing information. I will use Excel to help fill in this missing information.

Start by entering the above information on Excel and then plotting the points using a scatter-plot graph (fig1).

fig1.

The scatter-plot appears to behave like an exponential or a power function. Excel has a feature that allows the user to fit a function to a scatter-plot. To use this feature:

1) Click on the graph.

2) Go to Chart and Select “Add Trendline…”

3) Choose the type of function will likely fit the scatter-plot.

I first tried the power function. The power function provided an excellent fit to the scatter-plot (fig2).

fig 2.

To have Excel display the function and the R squared value, click on options when you are in the “Add Trendline…” box.

The power function for this scatter-plot and the R squared value turned out to be:

The R squared value indicates that this power function provides a very strong fit for the scatter-plot.

Hence, using this power function, I predicted the missing information (highlighted) to be:

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

I also tried an applying an exponential function (fig 3) and a polynomial function (fig 4) to the scatter-plot.

fig 3.

fig 4.

The exponential function did not provide as good of a fit as I initially thought it would. However, the polynomial function did provide a much better fit. Comparing the power and polynomial function (in parenthesis) predictions we see:

Age of Tree	100s Board Feet
20	1
40	6
60	16 (12)
80	33
100	56
120	88
140	134 (134)
160	182
180	251 (247)
200	320

Reflection:

As a math teacher, I never used Excel or any other spreadsheet to help with equation fitting in my classroom. I remember using a spreadsheet program on an Apple computer predict certain experiments when I was a high school student in a physics class. That was the last time I used a spreadsheet program for any type of function fitting. In my teaching career, I had my students use the graphing calculator to compute linear and quadratic regressions. I did not think about using Excel as a technology much because I did not know how to use many of the features of Excel myself.

After completing this assignment. I come to fully appreciate the power and possibilities of Excel in many math lessons in an Algebra 2 or a Pre-Calculus class. Its one of those things that I wish I knew when I was teaching.

RETURN