Using Excel to Find A Curve of Best Fit
Situation - The following data is from the lumber industry, giving the approxiate number of board feet of lumber per tree in a forest of a given age in years. What function will fit the data? Predict the harvest for ages other than those given.
Solution:
Step 1 - The first thing I did was create a scatter plot of the data.
Step 2: Analysis of the Graph - The data is certainly not linear, I'm thinking it's quadratic.
Step 3: Trial and Error - Try various quadratic functions to see if they "match" your data.
a) First trial : y = .0075x²
Data Table:
Graph of data compared to original data:
b) Second Trial : y = .008x²
Data Table:
Analysis: I felt this was close, but still a little low for lumber harvest for values ranging from 80 to 120 yrs.
c) At this point I felt I was pretty spot on, so I used regression analysis that came up with the curve: y = .001x² - .68x + 13.3 with R² = .999 (YAY!)
Data Table:
Graph of data against original data:
Analysis: Looks Good! Spot On! Don't you just love Statistics :-)?
c) Completing the table and finding the missing values. Going to let y = .001x² - .68x + 13.3
Data Table with Predicted Values:
