Using Excel to fit a damped sinusoid to experimental data:

y = A exp(-gt) cos(wt + f) [Eq. 1]

George Holzwarth

PREPARATION of RAW DATA FILE: Prepare a text file under DOSEDIT or a similar editor, with column headings labelled "t" and "y_obs" to denote time and experimental displacement. Enter successive t and y_obs values, using a tab to go from first to second column . Save file to floppy disk as a:\myrawdata.

PROCESS the raw data in EXCEL spreadsheet, as follows.

1. Open Excel. From the menu, choose:

• File
• Open, and then
  • a:
  • file
  • type *.*
  • myrawdata
• FileWizard will open automatically to help you import the data into the spreadsheet:
  • Wizard Step1: Set file origin to "DOS or OS2"
  • Wizard Step2: Set delimiter to "space"
    • You should see a vertical line between columns for t and y_th.
  • Wizard Step3: Tell Wizard to finish.. Wizard should import column headings and raw data into the spreadsheet.

2. Create new column headings y_fit and error for theoretical y-values computed by the above equation, and deviations between theory and experiment, y_fit - y_obs.

3. Set up cells for the parameter names and values, as follows:

In free column space to the right of your data, set up two short columns for the five unknowns and c², the function we will minimize to get the best fit:

Parameter	Value
A
gamma
w
phi
B
chi_squared

The unknowns A, gamma, w, and phi correspond in obvious ways to the parameters of eq. 1. Since the value of y_fit at t = is zero in equation 1, but not zero in the experiments, we must include an extra parameter B (for Baseline) for this offset. The parameter phi is, in a similar way, an offset of the t axis.

Next, create six empty boxes in the Value column, to hold the 6 unknowns:

• To create an empty cell to hold the value of "A",
  • Click on cell you wish to label
  • Insert: Name Define. Define as "A"(default):
• To create an empty cell to hold the value of "gamma," follow same procedure.

4. Enter approximate, estimated values of the 5 parameters in the empty cells created in step 3, for example A=10, gamma=0.001, w=0.01,phi=0, B = 50. Leave the chi_squared box empty.

5. Type in the procedure by which Excel should compute y_fit in Column C. If the first cell of y_fit is C2, and the first cell of t is A2,

• Double click on C2;
• Enter the formula to compute y_fit:
  

=A*EXP(-gamma*$A2)*cos(w*$A2+phi) - B

• Press enter, or click on the green check in the toolbar, to accept your entry. The theoretical value (a number) should appear in cell C2. If it does not, stop and fix your error.
• Copy the formula to the remaining cells in row C by dragging the copy handle on C2 down the column.,
• Values of y_fit should appear, based on your initial estimated guesses for the parameters in the equation.
  

• 6. Now compute the values of "error" in column D. Proceed as in step 5, but define the first cell in Column Error by the formula
  

• When done, copy the formula to the remaining cells in the Error column D3..DNN .
  

7. Compute c2 , the sum of the squares of the deviations between y_obs and y_fit, using Excel's "SUMSQ" function:

• Click on the empty chi_squared cell defined but not filled in Step 3
• type

=SUMSQ(D2:DNN)

8. Now ask Excel's SOLVER routine to minimize chi_squared:

• ToolsSolverlabel target cell where sumsq[D2..DNN] is located

• minimize
  • enter cell coordi nates of your guessed values of A, gamma, w, phi, and B

New values of the A, gamma, w, phi, B, and etc should be computed in a few seconds, and new values of y_fit should appear. If they do not, one way to trace your error is to use the "Audition" function under "Tools". It places arrows on the spreadsheet to tell how information flows from one cell to another.

9. You will find it useful to make plots of your raw data and, on the same graph, the data which are generated by the assumed functional form with its "best-fit" parameters.

• Highlight the t, y_obs, and y_fit columns needed for the graphs.
• Click on the ChartWizard icon;
• Use mouse to define location and size of desired graph;
• Set Chart Type to SCATTER(not Line!);
  • Pick the type of scatter plot you need; default is probably okay;
• To change y-axis range, double-click on the axis numbers
• To change line-style or symbol, double click on the line you wish to change. Showing the experimental data as points(symbols, no line) and the fitted results as a line(no points) makes for the most informative graph.
• To print, go to the Edit menu, Print submenu.
  

10. The fitted parameters A, w, etc should correspond closely to your manual estimates. Recalculate G using the fitted parameters from Excel.
[an error occurred while processing this directive]