MATH 109

2.5 Asmt

Name _________________________________________________________

The purpose of this assignment is to introduce you to curve fitting. Often you have data and you would like to represent the data with an equation so that you can make estimates and predictions. In this assignment you will create manual fits to data and use Microsoft Excel to do computer fits.

Manual Curve Fitting

STEPS:

1. Plot your data points on a graph very carefully

2. Draw the best curve (usually a straight like or a quadratic (one bump curve) through the data points. This is a visual best fit.

3. Extract enough data from the curve to create an equation. (2 points for a straight line, and three points for a quadratic)

4. Calculate the Equation.

MANUAL EXAMPLE: (see example 1 page 185)

Give data on the percentage of families with internet access we can construct the following table:

Year 1996 1997 1998 1999 2000 2001

Percent 8.5 14.3 26.2 28.6 41.5 50.5

To manually estimate an equation for this data, take a ruler and draw the visually best straight line that you can. It would hit all the points but it should be as close to all the points as you can make it. Do it carefully and use a ruler or other straight edge. (Now you have completed step 2 above.)

Carefully estimate two points (X1, Y1) and (X2, Y2) and then use the definitions of slope and the point and slope method to create a linear equation for the line.

Write the equation that you got here ______________________________________

LINEAR EQUATIONS WITH EXCEL

Let's do the same thing with Microsoft's Excel spreadsheet. Enter the data points in the table on page 1 into Excel (note that you're using years since 1995 for x)

Once you've entered the data, highlight the data points. You do that

by holding down the left mouse button and mousing on the data you

want highlight.

To make a chart select the chart wizard on the toolbar. Select the XY

(Scatter) option. You can insert labels on your chart if you like

similar to those I used on page 1. What we want to do is to use the

equation estimating properties of Excel.

To get an equation, mouse on the data points highlighting them. Then right click your mouse and select Add Trendline. The type will default to linear. Click on Options and select the check box that is marked Display Equation On Chart. Click OK and you should have an equation on your chart.

Write the equation that you got from Excel here ______________________________

Compare your manual equation with your Excel equation. They should be similar but not exactly the same. The computer uses a special criterion for picking the best straight line called the least-square error.

Excel can also do more complex curve fitting. The data shown below is from problem 29 in your text on page 193. The first column is the year, the second the year minus 1940, so years since 1940, and the third column is the Gross Domestic Product in Billions of dollars. The data is plotted on the right.

The two curves were generated with Excel using the Add Trendline functions both linear and a polynomial 3rd order fit. You should enter data in Excel and experiment. To move the data to Microsoft Word you can select the data range or the figure and copy and paste special. I always convert the selection to a picture and put it in front of the text because I find that the layout works better that way.

Now you should do it yourself. Enter the data from problem 30 (pg 193) into Excel and do a series of curves and print them out in a Word document and type a paragraph of text explaining what you did. Describe how well the curves fit the data. Use a linear, quadratic and cubic curve. Which curve fits the data the best? Is the best fit very good?

Do you think the data is likely to be consistent? Why or why not?

Another example is fun to look at. Look at Example 3 on page 187 which describes per capita tax.