Engineering 103 -UMass Boston

CW 3

(In-Class-Work 3)

Open Excel and type your first and last name in cell A1, today’s date in cell D1

Quadratic curve fitting with Excel:

Use Excel Solver (under Tools) to produce coefficients a,b,c for your quadratic model (y’=a*x²+b*x+c) by minimizing the ‘standard deviation’ s for the following set of data (, this is not a conventional standard deviation, and so it is not given by the ‘stdev’ function in Excel, but it is what we need to model our data; n is the number of data; y’ indicates values predicted by our model; y indicates measured values given as data). Use ‘=’ to start the formula for y’ in cell D3; click on the corresponding cells to enter the coefficients and variables into the formula (if a is in cell C3, then click on C3, if the first x is in A3, then click on A3, etc.); remember to add a ‘$’ before and after the letter of the cell containing the coefficients (since we don’t want these to change for the second, third, and fourth x’s); produce similar results for cells D4-D6 by ‘copy D2 and paste’ into those; produce the squared difference formula between y’ and y using another ‘=’ in E3; etc. Produce the average of E3-E6 by writing in E7 ‘=average(E3..E6)’ and hit enter. Click on E7 and pull out ‘Solver’ under ‘Tools’; select ‘minimum’ and in the ‘by changing’ box click and drag on the cells containing your guesses for the coefficients. Note that after running ‘Solver’, the ‘standard deviation’ or ‘how far is our model from the data’ is reduced to be a small number (0.1 or less, the smaller the better model you’ve built for those data). Watch the movies clip ‘curve-fitting with Excel’ as you follow these instructions.

Periods x (s) Lengths y (m)

4.5 5

6.35 10

7.75 15

9.2 20

Print out a copy to keep and one to submit

*Remember that this is an individual work (turn it in, as instructed, with your name and date). Home-works and class-works count 20% toward the course grade. Class-works are done in class.